查询

刷新物化视图

REFRESH MATERIALIZED view ods.v_sap_producttype_distinct

取分组的最大值数据

SELECT *
   FROM ods.v_sap_building_full t1
     JOIN ( SELECT vsbf.xm,
            max(vsbf.jhbb::text) AS jhbb
           FROM ods.v_sap_building_full vsbf
          GROUP BY vsbf.xm) latest ON t1.xm::text = latest.xm::text AND t1.jhbb::text = latest.jhbb;

用子查询取最大值后关联

模糊关联，比如佛山市关联佛山

select a.namepath,b.city from md_cityregion a left join z_project_temp b on a.name like concat(b.city,'%')

取楼栋阶段回退，A字段版本增加，B字段阶段存在回退的

select A.* 
FROM 
(
SELECT 
jhbb,
ld,
case "bzjd" 
when '土地获取阶段' then 0 
when '市场定位阶段' then 1 
when '方案阶段' then 2
when '施工图阶段' then 3
when '预测阶段' then 4
when '实测阶段' then 5
else 5 end bzjd
FROM "v_sap_building_full" 
) A inner join (
SELECT 
jhbb,
ld,
case "bzjd" 
when '土地获取阶段' then 0 
when '市场定位阶段' then 1 
when '方案阶段' then 2
when '施工图阶段' then 3
when '预测阶段' then 4
when '实测阶段' then 5
else 5 end bzjd
FROM "v_sap_building_full" 
) B ON A.ld = B.ld and A.jhbb > B.jhbb AND A.bzjd < B.bzjd

oracle使用union all 报字符集不匹配

虽然都是字符型但是如果使用了case when等转换，会报错。需要在union的层级转换字段类型

TO_CHAR(ZT.KEHU) AS KEHU

oracle分组聚合

把多个客户名称拼接成一个字符串

select listagg(CUST_NAME, ',') within group( order by CUST_NAME) as name 
from BI_CONNECT.ZSAC_D_CN_BP 
WHERE PARENT_KEY ='005056B9EED81EEA89A71B6693043135'

ORACLE递归生成时间

SELECT SYSDATE - LEVEL + 1 FROM DUAL CONNECT BY LEVEL <=5

MS SQL SERVER 变量

DECLARE @xiangmu char(8000)
DECLARE @bgndate char(10)
DECLARE @enddate char(10)
SET @xiangmu = 'tjgs.tjgs0008''gzgs.Proj0029'
SET @bgndate = '2021-01-01'
SET @enddate = '2021-04-01';
WITH DIM_quyu AS (
    -- dim 组织维度-区域
    SELECT

修改