奢侈品的网站设计,做网站实训心得体会,wordpress 共享按钮,贵阳装饰装修公司网站问题#xff1a;需要查询1月、1-2月、1-3月… 1-12月#xff0c;分区间的累计数据#xff0c;在同一个sql语句里面实现。 多个分开查询效率不高#xff0c;并且数据手动合并麻烦。
with t1 as (
SELECT *,CASE WHEN insutype 390 THEN 居民 ELSE 职工 END 人员类别,SUBST…问题需要查询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 t1WHERE 月份1and 2022年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP 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年度参保1GROUP by certno,人员类别
)
--SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,1 截至月份 FROM t2 GROUP by 人员类别,人头自费费用分组
,
t14 as (
SELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,1 截至月份 FROM t2 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,2 截至月份 FROM t3 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,3 截至月份 FROM t4 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,4 截至月份 FROM t5 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,5 截至月份 FROM t6 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,6 截至月份 FROM t7 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,7 截至月份 FROM t8 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,8 截至月份 FROM t9 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,9 截至月份 FROM t10 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,10 截至月份 FROM t11 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,11 截至月份 FROM t12 GROUP by 人员类别,人头自费费用分组
UNION ALLSELECT 人员类别, 人头自费费用分组,sum(自费) 自费,COUNT(certno) 人数,12 截至月份 FROM t13 GROUP by 人员类别,人头自费费用分组 )
SELECT * FROM t14;代码解读
t1 先做基本处理可以按照实际业务来。t2-t13 按每个月截至t14 将 t2-t13 和在一起最后进行输出结果
