sql解决取多个截至每个月的数据-Toy模板网

这篇具有很好参考价值的文章主要介绍了sql解决取多个截至每个月的数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

问题：需要查询1月、1-2月、1-3月… 1-12月，分区间的累计数据，在同一个sql语句里面实现。
多个分开查询效率不高，并且数据手动合并麻烦。

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 t1
    WHERE 
    月份<=1
    and 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 t1
    WHERE 
    月份<=2
    and 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 t1
    WHERE 
    月份<=3
    and 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 t1
    WHERE 
    月份<=4
    and 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 t1
    WHERE 
    月份<=5
    and 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 t1
    WHERE 
    月份<=6
    and 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 t1
    WHERE 
    月份<=7
    and 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 t1
    WHERE 
    月份<=8
    and 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 t1
    WHERE 
    月份<=9
    and 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 t1
    WHERE 
    月份<=10
    and 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 t1
    WHERE 
    月份<=11
    and 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 t1
    WHERE 
    月份<=12
    and 2022年度参保='1'

     GROUP by  certno,人员类别 
)
--SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,1 截至月份 FROM t2 GROUP by  人员类别,人头自费费用分组 
,
t14 as (
SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,1 截至月份 FROM t2 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,2 截至月份 FROM t3 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,3 截至月份 FROM t4 GROUP by  人员类别,人头自费费用分组  
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,4 截至月份 FROM t5 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,5 截至月份 FROM t6 GROUP by  人员类别,人头自费费用分组  
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,6 截至月份 FROM t7 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,7 截至月份 FROM t8 GROUP by  人员类别,人头自费费用分组  
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,8 截至月份 FROM t9 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,9 截至月份 FROM t10 GROUP by  人员类别,人头自费费用分组  
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,10 截至月份 FROM t11 GROUP by  人员类别,人头自费费用分组 
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,11 截至月份 FROM t12 GROUP by  人员类别,人头自费费用分组  
UNION  ALL
 SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,12 截至月份 FROM t13 GROUP by  人员类别,人头自费费用分组 
  
 )
SELECT * FROM t14;