問題：需要查詢1月、1-2月、1-3月… 1-12月，分區(qū)間的累計數(shù)據(jù)，在同一個sql語句里面實現(xiàn)。
多個分開查詢效率不高，并且數(shù)據(jù)手動合并麻煩。

with t1 as (
SELECT *,CASE WHEN insutype= '390' THEN '居民'  ELSE '職工' END 人員類別,SUBSTR(enddate,6,2)  月份 FROM sw_sx_20230821test_1_join_two_all_2 WHERE MED_TYPE IN ("21","210104","2403","28","52","71","9938","9940","140201") AND setl_type NOT in ("3")   -- 未退費and REFD_SETL_FLAG =0 -- 有效and VALI_FLAG = 1 and SUBSTR(enddate,1,4)='2022' and insutype in ('310','340','390') ) 
,
t2 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=1and 2022年度參保='1'GROUP by  certno,人員類別 
)
,
t3 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=2and 2022年度參保='1'GROUP by  certno,人員類別 
),
t4 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=3and 2022年度參保='1'GROUP by  certno,人員類別 
),
t5 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=4and 2022年度參保='1'GROUP by  certno,人員類別 
),
t6 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=5and 2022年度參保='1'GROUP by  certno,人員類別 
),
t7 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=6and 2022年度參保='1'GROUP by  certno,人員類別 
),
t8 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=7and 2022年度參保='1'GROUP by  certno,人員類別 
),
t9 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=8and 2022年度參保='1'GROUP by  certno,人員類別 
),
t10 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=9and 2022年度參保='1'GROUP by  certno,人員類別 
),
t11 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=10and 2022年度參保='1'GROUP by  certno,人員類別 
),
t12 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=11and 2022年度參保='1'GROUP by  certno,人員類別 
),
t13 as (SELECT certno,人員類別,sum(fulamt_ownpay_amt)+sum(overlmt_selfpay) 自費,FLOOR((sum(fulamt_ownpay_amt)+sum(overlmt_selfpay))/1000) AS 人頭自費費用分組FROM t1WHERE 月份<=12and 2022年度參保='1'GROUP by  certno,人員類別 
)
--SELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),1 截至月份 FROM t2 GROUP by  人員類別,人頭自費費用分組 
,
t14 as (
SELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),1 截至月份 FROM t2 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),2 截至月份 FROM t3 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),3 截至月份 FROM t4 GROUP by  人員類別,人頭自費費用分組  
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),4 截至月份 FROM t5 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),5 截至月份 FROM t6 GROUP by  人員類別,人頭自費費用分組  
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),6 截至月份 FROM t7 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),7 截至月份 FROM t8 GROUP by  人員類別,人頭自費費用分組  
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),8 截至月份 FROM t9 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),9 截至月份 FROM t10 GROUP by  人員類別,人頭自費費用分組  
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),10 截至月份 FROM t11 GROUP by  人員類別,人頭自費費用分組 
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),11 截至月份 FROM t12 GROUP by  人員類別,人頭自費費用分組  
UNION  ALLSELECT 人員類別, 人頭自費費用分組,sum(自費) 自費,COUNT(certno) 人數(shù),12 截至月份 FROM t13 GROUP by  人員類別,人頭自費費用分組 )
SELECT * FROM t14;

代碼解讀

1. t1 先做基本處理，可以按照實際業(yè)務來。
2. t2-t13 按每個月截至
3. t14 將 t2-t13 和在一起
4. 最后進行輸出結果