提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
数据库报表
前言
利用oracle语句完成数据库报表
一、报表详情
注:合计、预算占比、实际占比、Gap需要通过一定的计算得出,其中预算占比和实际占比需要通过预算总和、实际总和做除法得出
二、步骤
1.首先通过数据库查询出累计、一月至十二月的预算和实际预算的数据(Gap为实际减预算可一同得出)
代码如下(示例):
DETAIL AS
(
SELECT
SUBJECT_LEVEL1,
--年度累计
ROUND(YS_YEAR_FEE_TOTAL, 2) YS_YEAR_FEE_TOTAL,
ROUND(SJ_YEAR_FEE_TOTAL, 2) SJ_YEAR_FEE_TOTAL,
ROUND(SJ_YEAR_FEE_TOTAL - YS_YEAR_FEE_TOTAL, 2) YEAR_FEE_GAP,
--1月
ROUND(YS_MONTH1_FEE_TOTAL,2) YS_MONTH1_FEE_TOTAL,
ROUND(SJ_MONTH1_FEE_TOTAL, 2) SJ_MONTH1_FEE_TOTAL,
ROUND(SJ_MONTH1_FEE_TOTAL - YS_MONTH1_FEE_TOTAL, 2) MONTH1_FEE_GAP,
--2月
ROUND(YS_MONTH2_FEE_TOTAL, 2) YS_MONTH2_FEE_TOTAL,
ROUND(SJ_MONTH2_FEE_TOTAL, 2) SJ_MONTH2_FEE_TOTAL,
ROUND(SJ_MONTH2_FEE_TOTAL - YS_MONTH2_FEE_TOTAL, 2) MONTH2_FEE_GAP,
--3月
ROUND(YS_MONTH3_FEE_TOTAL, 2) YS_MONTH3_FEE_TOTAL,
ROUND(SJ_MONTH3_FEE_TOTAL, 2) SJ_MONTH3_FEE_TOTAL,
ROUND(SJ_MONTH3_FEE_TOTAL - YS_MONTH3_FEE_TOTAL, 2) MONTH3_FEE_GAP,
--4月
ROUND(YS_MONTH4_FEE_TOTAL, 2) YS_MONTH4_FEE_TOTAL,
ROUND(SJ_MONTH4_FEE_TOTAL, 2) SJ_MONTH4_FEE_TOTAL,
ROUND(SJ_MONTH4_FEE_TOTAL - YS_MONTH4_FEE_TOTAL, 2) MONTH4_FEE_GAP,
--5月
ROUND(YS_MONTH5_FEE_TOTAL, 2) YS_MONTH5_FEE_TOTAL,
ROUND(SJ_MONTH5_FEE_TOTAL, 2) SJ_MONTH5_FEE_TOTAL,
ROUND(SJ_MONTH5_FEE_TOTAL - YS_MONTH5_FEE_TOTAL, 2) MONTH5_FEE_GAP,
--6月
ROUND(YS_MONTH6_FEE_TOTAL, 2) YS_MONTH6_FEE_TOTAL,
ROUND(SJ_MONTH6_FEE_TOTAL, 2) SJ_MONTH6_FEE_TOTAL,
ROUND(SJ_MONTH6_FEE_TOTAL - YS_MONTH6_FEE_TOTAL, 2) MONTH6_FEE_GAP,
--7月
ROUND(YS_MONTH7_FEE_TOTAL, 2) YS_MONTH7_FEE_TOTAL,
ROUND(SJ_MONTH7_FEE_TOTAL, 2) SJ_MONTH7_FEE_TOTAL,
ROUND(SJ_MONTH7_FEE_TOTAL - YS_MONTH7_FEE_TOTAL, 2) MONTH7_FEE_GAP,
--8月
ROUND(YS_MONTH8_FEE_TOTAL, 2) YS_MONTH8_FEE_TOTAL,
ROUND(SJ_MONTH8_FEE_TOTAL, 2) SJ_MONTH8_FEE_TOTAL,
ROUND(SJ_MONTH8_FEE_TOTAL - YS_MONTH8_FEE_TOTAL, 2) MONTH8_FEE_GAP,
--9月
ROUND(YS_MONTH9_FEE_TOTAL, 2) YS_MONTH9_FEE_TOTAL,
ROUND(SJ_MONTH9_FEE_TOTAL, 2) SJ_MONTH9_FEE_TOTAL,
ROUND(SJ_MONTH9_FEE_TOTAL - YS_MONTH9_FEE_TOTAL, 2) MONTH9_FEE_GAP,
--10月
ROUND(YS_MONTH10_FEE_TOTAL, 2) YS_MONTH10_FEE_TOTAL,
ROUND(SJ_MONTH10_FEE_TOTAL, 2) SJ_MONTH10_FEE_TOTAL,
ROUND(SJ_MONTH10_FEE_TOTAL - YS_MONTH10_FEE_TOTAL, 2) MONTH10_FEE_GAP,
--11月
ROUND(YS_MONTH11_FEE_TOTAL, 2) YS_MONTH11_FEE_TOTAL,
ROUND(SJ_MONTH11_FEE_TOTAL, 2) SJ_MONTH11_FEE_TOTAL,
ROUND(SJ_MONTH11_FEE_TOTAL - YS_MONTH11_FEE_TOTAL, 2) MONTH11_FEE_GAP,
--12月
ROUND(YS_MONTH12_FEE_TOTAL, 2) YS_MONTH12_FEE_TOTAL,
ROUND(SJ_MONTH12_FEE_TOTAL, 2) SJ_MONTH12_FEE_TOTAL,
ROUND(SJ_MONTH12_FEE_TOTAL - YS_MONTH12_FEE_TOTAL, 2) MONTH12_FEE_GAP
from
(
select SUBJECT_LEVEL1,
SUM(CASE WHEN VERSION_CODE = '预算' then FEE_MONEY ELSE 0 end) YS_YEAR_FEE_TOTAL, --年度预算合计
SUM(CASE WHEN VERSION_CODE = '实际' then FEE_MONEY ELSE 0 end) SJ_YEAR_FEE_TOTAL, --年度实际合计
--月度明细
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '01' then FEE_MONEY ELSE 0 END) YS_MONTH1_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '01' then FEE_MONEY ELSE 0 END) SJ_MONTH1_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '02' then FEE_MONEY ELSE 0 END) YS_MONTH2_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '02' then FEE_MONEY ELSE 0 END) SJ_MONTH2_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '03' then FEE_MONEY ELSE 0 END) YS_MONTH3_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '03' then FEE_MONEY ELSE 0 END) SJ_MONTH3_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '04' then FEE_MONEY ELSE 0 END) YS_MONTH4_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '04' then FEE_MONEY ELSE 0 END) SJ_MONTH4_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '05' then FEE_MONEY ELSE 0 END) YS_MONTH5_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '05' then FEE_MONEY ELSE 0 END) SJ_MONTH5_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '06' then FEE_MONEY ELSE 0 END) YS_MONTH6_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '06' then FEE_MONEY ELSE 0 END) SJ_MONTH6_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '07' then FEE_MONEY ELSE 0 END) YS_MONTH7_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '07' then FEE_MONEY ELSE 0 END) SJ_MONTH7_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '08' then FEE_MONEY ELSE 0 END) YS_MONTH8_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '08' then FEE_MONEY ELSE 0 END) SJ_MONTH8_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '09' then FEE_MONEY ELSE 0 END) YS_MONTH9_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '09' then FEE_MONEY ELSE 0 END) SJ_MONTH9_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '10' then FEE_MONEY ELSE 0 END) YS_MONTH10_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '10' then FEE_MONEY ELSE 0 END) SJ_MONTH10_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '11' then FEE_MONEY ELSE 0 END) YS_MONTH11_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '11' then FEE_MONEY ELSE 0 END) SJ_MONTH11_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '12' then FEE_MONEY ELSE 0 END) YS_MONTH12_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '12' then FEE_MONEY ELSE 0 END) SJ_MONTH12_FEE_TOTAL
from BI_FINANCE_FEE_DETAIL
where IYEAR = #{input.year}
GROUP BY SUBJECT_LEVEL1
) temp
),
可得到一下红圈里的数据
2.计算累计、一月至十二月下的预算、实际总计,以便求各自下面的预算占比和实际占比
代码如下(示例):
tt AS
(
SELECT
'合计' SUBJECT_LEVEL1,
SUM(YS_YEAR_FEE_TOTAL) YS_YEAR_FEE_TOTAL,
SUM(SJ_YEAR_FEE_TOTAL) SJ_YEAR_FEE_TOTAL,
SUM(YEAR_FEE_GAP) YEAR_FEE_GAP,
--1月
SUM(YS_MONTH1_FEE_TOTAL) YS_MONTH1_FEE_TOTAL,
SUM(SJ_MONTH1_FEE_TOTAL) SJ_MONTH1_FEE_TOTAL,
SUM(MONTH1_FEE_GAP) MONTH1_FEE_GAP,
--2月
SUM(YS_MONTH2_FEE_TOTAL) YS_MONTH2_FEE_TOTAL,
SUM(SJ_MONTH2_FEE_TOTAL) SJ_MONTH2_FEE_TOTAL,
SUM(MONTH2_FEE_GAP) MONTH2_FEE_GAP,
--3月
SUM(YS_MONTH3_FEE_TOTAL) YS_MONTH3_FEE_TOTAL,
SUM(SJ_MONTH3_FEE_TOTAL) SJ_MONTH3_FEE_TOTAL,
SUM(MONTH3_FEE_GAP) MONTH3_FEE_GAP,
--4月
SUM(YS_MONTH4_FEE_TOTAL) YS_MONTH4_FEE_TOTAL,
SUM(SJ_MONTH4_FEE_TOTAL) SJ_MONTH4_FEE_TOTAL,
SUM(MONTH4_FEE_GAP) MONTH4_FEE_GAP,
--5月
SUM(YS_MONTH5_FEE_TOTAL) YS_MONTH5_FEE_TOTAL,
SUM(SJ_MONTH5_FEE_TOTAL) SJ_MONTH5_FEE_TOTAL,
SUM(MONTH5_FEE_GAP) MONTH5_FEE_GAP,
--6月
SUM(YS_MONTH6_FEE_TOTAL) YS_MONTH6_FEE_TOTAL,
SUM(SJ_MONTH6_FEE_TOTAL) SJ_MONTH6_FEE_TOTAL,
SUM(MONTH6_FEE_GAP) MONTH6_FEE_GAP,
--7月
SUM(YS_MONTH7_FEE_TOTAL) YS_MONTH7_FEE_TOTAL,
SUM(SJ_MONTH7_FEE_TOTAL) SJ_MONTH7_FEE_TOTAL,
SUM(MONTH7_FEE_GAP) MONTH7_FEE_GAP,
--8月
SUM(YS_MONTH8_FEE_TOTAL) YS_MONTH8_FEE_TOTAL,
SUM(SJ_MONTH8_FEE_TOTAL) SJ_MONTH8_FEE_TOTAL,
SUM(MONTH8_FEE_GAP) MONTH8_FEE_GAP,
--9月
SUM(YS_MONTH9_FEE_TOTAL) YS_MONTH9_FEE_TOTAL,
SUM(SJ_MONTH9_FEE_TOTAL) SJ_MONTH9_FEE_TOTAL,
SUM(MONTH9_FEE_GAP) MONTH9_FEE_GAP,
--10月
SUM(YS_MONTH10_FEE_TOTAL) YS_MONTH10_FEE_TOTAL,
SUM(SJ_MONTH10_FEE_TOTAL) SJ_MONTH10_FEE_TOTAL,
SUM(MONTH10_FEE_GAP) MONTH10_FEE_GAP,
--11月
SUM(YS_MONTH11_FEE_TOTAL) YS_MONTH11_FEE_TOTAL,
SUM(SJ_MONTH11_FEE_TOTAL) SJ_MONTH11_FEE_TOTAL,
SUM(MONTH11_FEE_GAP) MONTH11_FEE_GAP,
--12月
SUM(YS_MONTH12_FEE_TOTAL) YS_MONTH12_FEE_TOTAL,
SUM(SJ_MONTH12_FEE_TOTAL) SJ_MONTH12_FEE_TOTAL,
SUM(MONTH12_FEE_GAP) MONTH12_FEE_GAP,
10 seq
FROM DETAIL
),
同过上述代码可以得到合计数据,如下图:
3.计算各自下面的预算占比和实际占比
代码如下(示例):
ss AS
(
SELECT
a.SUBJECT_LEVEL1 SUBJECT_LEVEL1,
--年度预算和实际占比
a.YS_YEAR_FEE_TOTAL YS_YEAR_FEE_TOTAL,
CASE WHEN b.YS_YEAR_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_YEAR_FEE_TOTAL/b.YS_YEAR_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_YEAR_FEE_TOTAL_PROP,
a.SJ_YEAR_FEE_TOTAL SJ_YEAR_FEE_TOTAL,
CASE WHEN b.SJ_YEAR_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_YEAR_FEE_TOTAL/b.SJ_YEAR_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_YEAR_FEE_TOTAL_PROP,
a.YEAR_FEE_GAP YEAR_FEE_GAP,
--一月
a.YS_MONTH1_FEE_TOTAL YS_MONTH1_FEE_TOTAL,
CASE WHEN b.YS_MONTH1_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH1_FEE_TOTAL/b.YS_MONTH1_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH1_FEE_TOTAL_PROP,
a.SJ_MONTH1_FEE_TOTAL SJ_MONTH1_FEE_TOTAL,
CASE WHEN b.SJ_MONTH1_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH1_FEE_TOTAL/b.SJ_MONTH1_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH1_FEE_TOTAL_PROP,
a.MONTH1_FEE_GAP,
--二月
a.YS_MONTH2_FEE_TOTAL YS_MONTH2_FEE_TOTAL,
CASE WHEN b.YS_MONTH2_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH2_FEE_TOTAL/b.YS_MONTH2_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH2_FEE_TOTAL_PROP,
a.SJ_MONTH2_FEE_TOTAL SJ_MONTH2_FEE_TOTAL,
CASE WHEN b.SJ_MONTH2_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH2_FEE_TOTAL/b.SJ_MONTH2_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH2_FEE_TOTAL_PROP,
a.MONTH2_FEE_GAP,
--三月
a.YS_MONTH3_FEE_TOTAL YS_MONTH3_FEE_TOTAL,
CASE WHEN b.YS_MONTH3_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH3_FEE_TOTAL/b.YS_MONTH3_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH3_FEE_TOTAL_PROP,
a.SJ_MONTH3_FEE_TOTAL SJ_MONTH3_FEE_TOTAL,
CASE WHEN b.SJ_MONTH3_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH3_FEE_TOTAL/b.SJ_MONTH3_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH3_FEE_TOTAL_PROP,
a.MONTH3_FEE_GAP,
--四月
a.YS_MONTH4_FEE_TOTAL YS_MONTH4_FEE_TOTAL,
CASE WHEN b.YS_MONTH4_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH4_FEE_TOTAL/b.YS_MONTH4_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH4_FEE_TOTAL_PROP,
a.SJ_MONTH4_FEE_TOTAL SJ_MONTH4_FEE_TOTAL,
CASE WHEN b.SJ_MONTH4_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH4_FEE_TOTAL/b.SJ_MONTH4_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH4_FEE_TOTAL_PROP,
a.MONTH4_FEE_GAP,
--五月
a.YS_MONTH5_FEE_TOTAL YS_MONTH5_FEE_TOTAL,
CASE WHEN b.YS_MONTH5_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH5_FEE_TOTAL/b.YS_MONTH5_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH5_FEE_TOTAL_PROP,
a.SJ_MONTH5_FEE_TOTAL SJ_MONTH5_FEE_TOTAL,
CASE WHEN b.SJ_MONTH5_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH5_FEE_TOTAL/b.SJ_MONTH5_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH5_FEE_TOTAL_PROP,
a.MONTH5_FEE_GAP,
--六月
a.YS_MONTH6_FEE_TOTAL YS_MONTH6_FEE_TOTAL,
CASE WHEN b.YS_MONTH6_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH6_FEE_TOTAL/b.YS_MONTH6_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH6_FEE_TOTAL_PROP,
a.SJ_MONTH6_FEE_TOTAL SJ_MONTH6_FEE_TOTAL,
CASE WHEN b.SJ_MONTH6_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH6_FEE_TOTAL/b.SJ_MONTH6_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH6_FEE_TOTAL_PROP,
a.MONTH6_FEE_GAP,
--七月
a.YS_MONTH7_FEE_TOTAL YS_MONTH7_FEE_TOTAL,
CASE WHEN b.YS_MONTH7_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH7_FEE_TOTAL/b.YS_MONTH7_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH7_FEE_TOTAL_PROP,
a.SJ_MONTH7_FEE_TOTAL SJ_MONTH7_FEE_TOTAL,
CASE WHEN b.SJ_MONTH7_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH7_FEE_TOTAL/b.SJ_MONTH7_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH7_FEE_TOTAL_PROP,
a.MONTH7_FEE_GAP,
--八月
a.YS_MONTH8_FEE_TOTAL YS_MONTH8_FEE_TOTAL,
CASE WHEN b.YS_MONTH8_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH8_FEE_TOTAL/b.YS_MONTH8_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH8_FEE_TOTAL_PROP,
a.SJ_MONTH8_FEE_TOTAL SJ_MONTH8_FEE_TOTAL,
CASE WHEN b.SJ_MONTH8_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH8_FEE_TOTAL/b.SJ_MONTH8_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH8_FEE_TOTAL_PROP,
a.MONTH8_FEE_GAP,
--九月
a.YS_MONTH9_FEE_TOTAL YS_MONTH9_FEE_TOTAL,
CASE WHEN b.YS_MONTH9_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH9_FEE_TOTAL/b.YS_MONTH9_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH9_FEE_TOTAL_PROP,
a.SJ_MONTH9_FEE_TOTAL SJ_MONTH9_FEE_TOTAL,
CASE WHEN b.SJ_MONTH9_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH9_FEE_TOTAL/b.SJ_MONTH9_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH9_FEE_TOTAL_PROP,
a.MONTH9_FEE_GAP,
--十月
a.YS_MONTH10_FEE_TOTAL YS_MONTH10_FEE_TOTAL,
CASE WHEN b.YS_MONTH10_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH10_FEE_TOTAL/b.YS_MONTH10_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH10_FEE_TOTAL_PROP,
a.SJ_MONTH10_FEE_TOTAL SJ_MONTH10_FEE_TOTAL,
CASE WHEN b.SJ_MONTH10_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH10_FEE_TOTAL/b.SJ_MONTH10_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH10_FEE_TOTAL_PROP,
a.MONTH10_FEE_GAP,
--十一月
a.YS_MONTH11_FEE_TOTAL YS_MONTH11_FEE_TOTAL,
CASE WHEN b.YS_MONTH11_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH11_FEE_TOTAL/b.YS_MONTH11_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH11_FEE_TOTAL_PROP,
a.SJ_MONTH11_FEE_TOTAL SJ_MONTH11_FEE_TOTAL,
CASE WHEN b.SJ_MONTH11_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH11_FEE_TOTAL/b.SJ_MONTH11_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH11_FEE_TOTAL_PROP,
a.MONTH11_FEE_GAP,
--十二月
a.YS_MONTH12_FEE_TOTAL YS_MONTH12_FEE_TOTAL,
CASE WHEN b.YS_MONTH12_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH12_FEE_TOTAL/b.YS_MONTH12_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH12_FEE_TOTAL_PROP,
a.SJ_MONTH12_FEE_TOTAL SJ_MONTH12_FEE_TOTAL,
CASE WHEN b.SJ_MONTH12_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH12_FEE_TOTAL/b.SJ_MONTH12_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH12_FEE_TOTAL_PROP,
a.MONTH12_FEE_GAP
FROM DETAIL a
INNER JOIN tt b ON 1 = 1
)
同过上述代码可以得到各自的占比,如下图:
4.将ss数据和合计表连接
即将红黄合并完成报表
代码如下(示例):
SELECT * FROM
(
SELECT ss.*, 4 seq
FROM ss
UNION ALL
SELECT
'合计' SUBJECT_LEVEL1,
SUM(YS_YEAR_FEE_TOTAL) YS_YEAR_FEE_TOTAL,
'100%' YS_YEAR_FEE_TOTAL_PROP,
SUM(SJ_YEAR_FEE_TOTAL) SJ_YEAR_FEE_TOTAL,
'100%' SJ_YEAR_FEE_TOTAL_PROP,
SUM(YEAR_FEE_GAP) YEAR_FEE_GAP,
--1月
SUM(YS_MONTH1_FEE_TOTAL) YS_MONTH1_FEE_TOTAL,
'100%' YS_MONTH1_FEE_TOTAL_PROP,
SUM(SJ_MONTH1_FEE_TOTAL) SJ_MONTH1_FEE_TOTAL,
'100%' SJ_MONTH1_FEE_TOTAL_PROP,
SUM(MONTH1_FEE_GAP) MONTH1_FEE_GAP,
--2月
SUM(YS_MONTH2_FEE_TOTAL) YS_MONTH2_FEE_TOTAL,
'100%' YS_MONTH2_FEE_TOTAL_PROP,
SUM(SJ_MONTH2_FEE_TOTAL) SJ_MONTH2_FEE_TOTAL,
'100%' SJ_MONTH2_FEE_TOTAL_PROP,
SUM(MONTH2_FEE_GAP) MONTH2_FEE_GAP,
--3月
SUM(YS_MONTH3_FEE_TOTAL) YS_MONTH3_FEE_TOTAL,
'100%' YS_MONTH3_FEE_TOTAL_PROP,
SUM(SJ_MONTH3_FEE_TOTAL) SJ_MONTH3_FEE_TOTAL,
'100%' SJ_MONTH3_FEE_TOTAL_PROP,
SUM(MONTH3_FEE_GAP) MONTH3_FEE_GAP,
--4月
SUM(YS_MONTH4_FEE_TOTAL) YS_MONTH4_FEE_TOTAL,
'100%' YS_MONTH4_FEE_TOTAL_PROP,
SUM(SJ_MONTH4_FEE_TOTAL) SJ_MONTH4_FEE_TOTAL,
'100%' SJ_MONTH4_FEE_TOTAL_PROP,
SUM(MONTH4_FEE_GAP) MONTH4_FEE_GAP,
--5月
SUM(YS_MONTH5_FEE_TOTAL) YS_MONTH5_FEE_TOTAL,
'100%' YS_MONTH5_FEE_TOTAL_PROP,
SUM(SJ_MONTH5_FEE_TOTAL) SJ_MONTH5_FEE_TOTAL,
'100%' SJ_MONTH5_FEE_TOTAL_PROP,
SUM(MONTH5_FEE_GAP) MONTH5_FEE_GAP,
--6月
SUM(YS_MONTH6_FEE_TOTAL) YS_MONTH6_FEE_TOTAL,
'100%' YS_MONTH6_FEE_TOTAL_PROP,
SUM(SJ_MONTH6_FEE_TOTAL) SJ_MONTH6_FEE_TOTAL,
'100%' SJ_MONTH6_FEE_TOTAL_PROP,
SUM(MONTH6_FEE_GAP) MONTH6_FEE_GAP,
--7月
SUM(YS_MONTH7_FEE_TOTAL) YS_MONTH7_FEE_TOTAL,
'100%' YS_MONTH7_FEE_TOTAL_PROP,
SUM(SJ_MONTH7_FEE_TOTAL) SJ_MONTH7_FEE_TOTAL,
'100%' SJ_MONTH7_FEE_TOTAL_PROP,
SUM(MONTH7_FEE_GAP) MONTH7_FEE_GAP,
--8月
SUM(YS_MONTH8_FEE_TOTAL) YS_MONTH8_FEE_TOTAL,
'100%' YS_MONTH8_FEE_TOTAL_PROP,
SUM(SJ_MONTH8_FEE_TOTAL) SJ_MONTH8_FEE_TOTAL,
'100%' SJ_MONTH8_FEE_TOTAL_PROP,
SUM(MONTH8_FEE_GAP) MONTH8_FEE_GAP,
--9月
SUM(YS_MONTH9_FEE_TOTAL) YS_MONTH9_FEE_TOTAL,
'100%' YS_MONTH9_FEE_TOTAL_PROP,
SUM(SJ_MONTH9_FEE_TOTAL) SJ_MONTH9_FEE_TOTAL,
'100%' SJ_MONTH9_FEE_TOTAL_PROP,
SUM(MONTH9_FEE_GAP) MONTH9_FEE_GAP,
--10月
SUM(YS_MONTH10_FEE_TOTAL) YS_MONTH10_FEE_TOTAL,
'100%' YS_MONTH10_FEE_TOTAL_PROP,
SUM(SJ_MONTH10_FEE_TOTAL) SJ_MONTH10_FEE_TOTAL,
'100%' SJ_MONTH10_FEE_TOTAL_PROP,
SUM(MONTH10_FEE_GAP) MONTH10_FEE_GAP,
--11月
SUM(YS_MONTH11_FEE_TOTAL) YS_MONTH11_FEE_TOTAL,
'100%' YS_MONTH11_FEE_TOTAL_PROP,
SUM(SJ_MONTH11_FEE_TOTAL) SJ_MONTH11_FEE_TOTAL,
'100%' SJ_MONTH11_FEE_TOTAL_PROP,
SUM(MONTH11_FEE_GAP) MONTH11_FEE_GAP,
--12月
SUM(YS_MONTH12_FEE_TOTAL) YS_MONTH12_FEE_TOTAL,
'100%' YS_MONTH12_FEE_TOTAL_PROP,
SUM(SJ_MONTH12_FEE_TOTAL) SJ_MONTH12_FEE_TOTAL,
'100%' SJ_MONTH12_FEE_TOTAL_PROP,
SUM(MONTH12_FEE_GAP) MONTH12_FEE_GAP,
16 seq
FROM ss
) nn
ORDER BY seq
5.完整代码
WITH
DETAIL AS
(
SELECT
SUBJECT_LEVEL1,
--年度累计
ROUND(YS_YEAR_FEE_TOTAL, 2) YS_YEAR_FEE_TOTAL,
ROUND(SJ_YEAR_FEE_TOTAL, 2) SJ_YEAR_FEE_TOTAL,
ROUND(SJ_YEAR_FEE_TOTAL - YS_YEAR_FEE_TOTAL, 2) YEAR_FEE_GAP,
--1月
ROUND(YS_MONTH1_FEE_TOTAL,2) YS_MONTH1_FEE_TOTAL,
ROUND(SJ_MONTH1_FEE_TOTAL, 2) SJ_MONTH1_FEE_TOTAL,
ROUND(SJ_MONTH1_FEE_TOTAL - YS_MONTH1_FEE_TOTAL, 2) MONTH1_FEE_GAP,
--2月
ROUND(YS_MONTH2_FEE_TOTAL, 2) YS_MONTH2_FEE_TOTAL,
ROUND(SJ_MONTH2_FEE_TOTAL, 2) SJ_MONTH2_FEE_TOTAL,
ROUND(SJ_MONTH2_FEE_TOTAL - YS_MONTH2_FEE_TOTAL, 2) MONTH2_FEE_GAP,
--3月
ROUND(YS_MONTH3_FEE_TOTAL, 2) YS_MONTH3_FEE_TOTAL,
ROUND(SJ_MONTH3_FEE_TOTAL, 2) SJ_MONTH3_FEE_TOTAL,
ROUND(SJ_MONTH3_FEE_TOTAL - YS_MONTH3_FEE_TOTAL, 2) MONTH3_FEE_GAP,
--4月
ROUND(YS_MONTH4_FEE_TOTAL, 2) YS_MONTH4_FEE_TOTAL,
ROUND(SJ_MONTH4_FEE_TOTAL, 2) SJ_MONTH4_FEE_TOTAL,
ROUND(SJ_MONTH4_FEE_TOTAL - YS_MONTH4_FEE_TOTAL, 2) MONTH4_FEE_GAP,
--5月
ROUND(YS_MONTH5_FEE_TOTAL, 2) YS_MONTH5_FEE_TOTAL,
ROUND(SJ_MONTH5_FEE_TOTAL, 2) SJ_MONTH5_FEE_TOTAL,
ROUND(SJ_MONTH5_FEE_TOTAL - YS_MONTH5_FEE_TOTAL, 2) MONTH5_FEE_GAP,
--6月
ROUND(YS_MONTH6_FEE_TOTAL, 2) YS_MONTH6_FEE_TOTAL,
ROUND(SJ_MONTH6_FEE_TOTAL, 2) SJ_MONTH6_FEE_TOTAL,
ROUND(SJ_MONTH6_FEE_TOTAL - YS_MONTH6_FEE_TOTAL, 2) MONTH6_FEE_GAP,
--7月
ROUND(YS_MONTH7_FEE_TOTAL, 2) YS_MONTH7_FEE_TOTAL,
ROUND(SJ_MONTH7_FEE_TOTAL, 2) SJ_MONTH7_FEE_TOTAL,
ROUND(SJ_MONTH7_FEE_TOTAL - YS_MONTH7_FEE_TOTAL, 2) MONTH7_FEE_GAP,
--8月
ROUND(YS_MONTH8_FEE_TOTAL, 2) YS_MONTH8_FEE_TOTAL,
ROUND(SJ_MONTH8_FEE_TOTAL, 2) SJ_MONTH8_FEE_TOTAL,
ROUND(SJ_MONTH8_FEE_TOTAL - YS_MONTH8_FEE_TOTAL, 2) MONTH8_FEE_GAP,
--9月
ROUND(YS_MONTH9_FEE_TOTAL, 2) YS_MONTH9_FEE_TOTAL,
ROUND(SJ_MONTH9_FEE_TOTAL, 2) SJ_MONTH9_FEE_TOTAL,
ROUND(SJ_MONTH9_FEE_TOTAL - YS_MONTH9_FEE_TOTAL, 2) MONTH9_FEE_GAP,
--10月
ROUND(YS_MONTH10_FEE_TOTAL, 2) YS_MONTH10_FEE_TOTAL,
ROUND(SJ_MONTH10_FEE_TOTAL, 2) SJ_MONTH10_FEE_TOTAL,
ROUND(SJ_MONTH10_FEE_TOTAL - YS_MONTH10_FEE_TOTAL, 2) MONTH10_FEE_GAP,
--11月
ROUND(YS_MONTH11_FEE_TOTAL, 2) YS_MONTH11_FEE_TOTAL,
ROUND(SJ_MONTH11_FEE_TOTAL, 2) SJ_MONTH11_FEE_TOTAL,
ROUND(SJ_MONTH11_FEE_TOTAL - YS_MONTH11_FEE_TOTAL, 2) MONTH11_FEE_GAP,
--12月
ROUND(YS_MONTH12_FEE_TOTAL, 2) YS_MONTH12_FEE_TOTAL,
ROUND(SJ_MONTH12_FEE_TOTAL, 2) SJ_MONTH12_FEE_TOTAL,
ROUND(SJ_MONTH12_FEE_TOTAL - YS_MONTH12_FEE_TOTAL, 2) MONTH12_FEE_GAP
from
(
select SUBJECT_LEVEL1,
SUM(CASE WHEN VERSION_CODE = '预算' then FEE_MONEY ELSE 0 end) YS_YEAR_FEE_TOTAL, --年度预算合计
SUM(CASE WHEN VERSION_CODE = '实际' then FEE_MONEY ELSE 0 end) SJ_YEAR_FEE_TOTAL, --年度实际合计
--月度明细
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '01' then FEE_MONEY ELSE 0 END) YS_MONTH1_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '01' then FEE_MONEY ELSE 0 END) SJ_MONTH1_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '02' then FEE_MONEY ELSE 0 END) YS_MONTH2_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '02' then FEE_MONEY ELSE 0 END) SJ_MONTH2_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '03' then FEE_MONEY ELSE 0 END) YS_MONTH3_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '03' then FEE_MONEY ELSE 0 END) SJ_MONTH3_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '04' then FEE_MONEY ELSE 0 END) YS_MONTH4_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '04' then FEE_MONEY ELSE 0 END) SJ_MONTH4_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '05' then FEE_MONEY ELSE 0 END) YS_MONTH5_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '05' then FEE_MONEY ELSE 0 END) SJ_MONTH5_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '06' then FEE_MONEY ELSE 0 END) YS_MONTH6_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '06' then FEE_MONEY ELSE 0 END) SJ_MONTH6_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '07' then FEE_MONEY ELSE 0 END) YS_MONTH7_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '07' then FEE_MONEY ELSE 0 END) SJ_MONTH7_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '08' then FEE_MONEY ELSE 0 END) YS_MONTH8_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '08' then FEE_MONEY ELSE 0 END) SJ_MONTH8_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '09' then FEE_MONEY ELSE 0 END) YS_MONTH9_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '09' then FEE_MONEY ELSE 0 END) SJ_MONTH9_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '10' then FEE_MONEY ELSE 0 END) YS_MONTH10_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '10' then FEE_MONEY ELSE 0 END) SJ_MONTH10_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '11' then FEE_MONEY ELSE 0 END) YS_MONTH11_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '11' then FEE_MONEY ELSE 0 END) SJ_MONTH11_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '预算' and SUBSTR(YEAR_MONTH, 5, 2) = '12' then FEE_MONEY ELSE 0 END) YS_MONTH12_FEE_TOTAL,
SUM(case WHEN VERSION_CODE = '实际' and SUBSTR(YEAR_MONTH, 5, 2) = '12' then FEE_MONEY ELSE 0 END) SJ_MONTH12_FEE_TOTAL
from BI_FINANCE_FEE_DETAIL
where IYEAR = 2022
GROUP BY SUBJECT_LEVEL1
) temp
),
tt AS
(
SELECT
'合计' SUBJECT_LEVEL1,
SUM(YS_YEAR_FEE_TOTAL) YS_YEAR_FEE_TOTAL,
SUM(SJ_YEAR_FEE_TOTAL) SJ_YEAR_FEE_TOTAL,
SUM(YEAR_FEE_GAP) YEAR_FEE_GAP,
--1月
SUM(YS_MONTH1_FEE_TOTAL) YS_MONTH1_FEE_TOTAL,
SUM(SJ_MONTH1_FEE_TOTAL) SJ_MONTH1_FEE_TOTAL,
SUM(MONTH1_FEE_GAP) MONTH1_FEE_GAP,
--2月
SUM(YS_MONTH2_FEE_TOTAL) YS_MONTH2_FEE_TOTAL,
SUM(SJ_MONTH2_FEE_TOTAL) SJ_MONTH2_FEE_TOTAL,
SUM(MONTH2_FEE_GAP) MONTH2_FEE_GAP,
--3月
SUM(YS_MONTH3_FEE_TOTAL) YS_MONTH3_FEE_TOTAL,
SUM(SJ_MONTH3_FEE_TOTAL) SJ_MONTH3_FEE_TOTAL,
SUM(MONTH3_FEE_GAP) MONTH3_FEE_GAP,
--4月
SUM(YS_MONTH4_FEE_TOTAL) YS_MONTH4_FEE_TOTAL,
SUM(SJ_MONTH4_FEE_TOTAL) SJ_MONTH4_FEE_TOTAL,
SUM(MONTH4_FEE_GAP) MONTH4_FEE_GAP,
--5月
SUM(YS_MONTH5_FEE_TOTAL) YS_MONTH5_FEE_TOTAL,
SUM(SJ_MONTH5_FEE_TOTAL) SJ_MONTH5_FEE_TOTAL,
SUM(MONTH5_FEE_GAP) MONTH5_FEE_GAP,
--6月
SUM(YS_MONTH6_FEE_TOTAL) YS_MONTH6_FEE_TOTAL,
SUM(SJ_MONTH6_FEE_TOTAL) SJ_MONTH6_FEE_TOTAL,
SUM(MONTH6_FEE_GAP) MONTH6_FEE_GAP,
--7月
SUM(YS_MONTH7_FEE_TOTAL) YS_MONTH7_FEE_TOTAL,
SUM(SJ_MONTH7_FEE_TOTAL) SJ_MONTH7_FEE_TOTAL,
SUM(MONTH7_FEE_GAP) MONTH7_FEE_GAP,
--8月
SUM(YS_MONTH8_FEE_TOTAL) YS_MONTH8_FEE_TOTAL,
SUM(SJ_MONTH8_FEE_TOTAL) SJ_MONTH8_FEE_TOTAL,
SUM(MONTH8_FEE_GAP) MONTH8_FEE_GAP,
--9月
SUM(YS_MONTH9_FEE_TOTAL) YS_MONTH9_FEE_TOTAL,
SUM(SJ_MONTH9_FEE_TOTAL) SJ_MONTH9_FEE_TOTAL,
SUM(MONTH9_FEE_GAP) MONTH9_FEE_GAP,
--10月
SUM(YS_MONTH10_FEE_TOTAL) YS_MONTH10_FEE_TOTAL,
SUM(SJ_MONTH10_FEE_TOTAL) SJ_MONTH10_FEE_TOTAL,
SUM(MONTH10_FEE_GAP) MONTH10_FEE_GAP,
--11月
SUM(YS_MONTH11_FEE_TOTAL) YS_MONTH11_FEE_TOTAL,
SUM(SJ_MONTH11_FEE_TOTAL) SJ_MONTH11_FEE_TOTAL,
SUM(MONTH11_FEE_GAP) MONTH11_FEE_GAP,
--12月
SUM(YS_MONTH12_FEE_TOTAL) YS_MONTH12_FEE_TOTAL,
SUM(SJ_MONTH12_FEE_TOTAL) SJ_MONTH12_FEE_TOTAL,
SUM(MONTH12_FEE_GAP) MONTH12_FEE_GAP,
10 seq
FROM DETAIL
),
ss AS
(
SELECT
a.SUBJECT_LEVEL1 SUBJECT_LEVEL1,
--年度预算和实际占比
a.YS_YEAR_FEE_TOTAL YS_YEAR_FEE_TOTAL,
CASE WHEN b.YS_YEAR_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_YEAR_FEE_TOTAL/b.YS_YEAR_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_YEAR_FEE_TOTAL_PROP,
a.SJ_YEAR_FEE_TOTAL SJ_YEAR_FEE_TOTAL,
CASE WHEN b.SJ_YEAR_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_YEAR_FEE_TOTAL/b.SJ_YEAR_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_YEAR_FEE_TOTAL_PROP,
a.YEAR_FEE_GAP YEAR_FEE_GAP,
--一月
a.YS_MONTH1_FEE_TOTAL YS_MONTH1_FEE_TOTAL,
CASE WHEN b.YS_MONTH1_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH1_FEE_TOTAL/b.YS_MONTH1_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH1_FEE_TOTAL_PROP,
a.SJ_MONTH1_FEE_TOTAL SJ_MONTH1_FEE_TOTAL,
CASE WHEN b.SJ_MONTH1_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH1_FEE_TOTAL/b.SJ_MONTH1_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH1_FEE_TOTAL_PROP,
a.MONTH1_FEE_GAP,
--二月
a.YS_MONTH2_FEE_TOTAL YS_MONTH2_FEE_TOTAL,
CASE WHEN b.YS_MONTH2_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH2_FEE_TOTAL/b.YS_MONTH2_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH2_FEE_TOTAL_PROP,
a.SJ_MONTH2_FEE_TOTAL SJ_MONTH2_FEE_TOTAL,
CASE WHEN b.SJ_MONTH2_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH2_FEE_TOTAL/b.SJ_MONTH2_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH2_FEE_TOTAL_PROP,
a.MONTH2_FEE_GAP,
--三月
a.YS_MONTH3_FEE_TOTAL YS_MONTH3_FEE_TOTAL,
CASE WHEN b.YS_MONTH3_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH3_FEE_TOTAL/b.YS_MONTH3_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH3_FEE_TOTAL_PROP,
a.SJ_MONTH3_FEE_TOTAL SJ_MONTH3_FEE_TOTAL,
CASE WHEN b.SJ_MONTH3_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH3_FEE_TOTAL/b.SJ_MONTH3_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH3_FEE_TOTAL_PROP,
a.MONTH3_FEE_GAP,
--四月
a.YS_MONTH4_FEE_TOTAL YS_MONTH4_FEE_TOTAL,
CASE WHEN b.YS_MONTH4_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH4_FEE_TOTAL/b.YS_MONTH4_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH4_FEE_TOTAL_PROP,
a.SJ_MONTH4_FEE_TOTAL SJ_MONTH4_FEE_TOTAL,
CASE WHEN b.SJ_MONTH4_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH4_FEE_TOTAL/b.SJ_MONTH4_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH4_FEE_TOTAL_PROP,
a.MONTH4_FEE_GAP,
--五月
a.YS_MONTH5_FEE_TOTAL YS_MONTH5_FEE_TOTAL,
CASE WHEN b.YS_MONTH5_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH5_FEE_TOTAL/b.YS_MONTH5_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH5_FEE_TOTAL_PROP,
a.SJ_MONTH5_FEE_TOTAL SJ_MONTH5_FEE_TOTAL,
CASE WHEN b.SJ_MONTH5_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH5_FEE_TOTAL/b.SJ_MONTH5_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH5_FEE_TOTAL_PROP,
a.MONTH5_FEE_GAP,
--六月
a.YS_MONTH6_FEE_TOTAL YS_MONTH6_FEE_TOTAL,
CASE WHEN b.YS_MONTH6_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH6_FEE_TOTAL/b.YS_MONTH6_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH6_FEE_TOTAL_PROP,
a.SJ_MONTH6_FEE_TOTAL SJ_MONTH6_FEE_TOTAL,
CASE WHEN b.SJ_MONTH6_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH6_FEE_TOTAL/b.SJ_MONTH6_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH6_FEE_TOTAL_PROP,
a.MONTH6_FEE_GAP,
--七月
a.YS_MONTH7_FEE_TOTAL YS_MONTH7_FEE_TOTAL,
CASE WHEN b.YS_MONTH7_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH7_FEE_TOTAL/b.YS_MONTH7_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH7_FEE_TOTAL_PROP,
a.SJ_MONTH7_FEE_TOTAL SJ_MONTH7_FEE_TOTAL,
CASE WHEN b.SJ_MONTH7_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH7_FEE_TOTAL/b.SJ_MONTH7_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH7_FEE_TOTAL_PROP,
a.MONTH7_FEE_GAP,
--八月
a.YS_MONTH8_FEE_TOTAL YS_MONTH8_FEE_TOTAL,
CASE WHEN b.YS_MONTH8_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH8_FEE_TOTAL/b.YS_MONTH8_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH8_FEE_TOTAL_PROP,
a.SJ_MONTH8_FEE_TOTAL SJ_MONTH8_FEE_TOTAL,
CASE WHEN b.SJ_MONTH8_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH8_FEE_TOTAL/b.SJ_MONTH8_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH8_FEE_TOTAL_PROP,
a.MONTH8_FEE_GAP,
--九月
a.YS_MONTH9_FEE_TOTAL YS_MONTH9_FEE_TOTAL,
CASE WHEN b.YS_MONTH9_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH9_FEE_TOTAL/b.YS_MONTH9_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH9_FEE_TOTAL_PROP,
a.SJ_MONTH9_FEE_TOTAL SJ_MONTH9_FEE_TOTAL,
CASE WHEN b.SJ_MONTH9_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH9_FEE_TOTAL/b.SJ_MONTH9_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH9_FEE_TOTAL_PROP,
a.MONTH9_FEE_GAP,
--十月
a.YS_MONTH10_FEE_TOTAL YS_MONTH10_FEE_TOTAL,
CASE WHEN b.YS_MONTH10_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH10_FEE_TOTAL/b.YS_MONTH10_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH10_FEE_TOTAL_PROP,
a.SJ_MONTH10_FEE_TOTAL SJ_MONTH10_FEE_TOTAL,
CASE WHEN b.SJ_MONTH10_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH10_FEE_TOTAL/b.SJ_MONTH10_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH10_FEE_TOTAL_PROP,
a.MONTH10_FEE_GAP,
--十一月
a.YS_MONTH11_FEE_TOTAL YS_MONTH11_FEE_TOTAL,
CASE WHEN b.YS_MONTH11_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH11_FEE_TOTAL/b.YS_MONTH11_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH11_FEE_TOTAL_PROP,
a.SJ_MONTH11_FEE_TOTAL SJ_MONTH11_FEE_TOTAL,
CASE WHEN b.SJ_MONTH11_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH11_FEE_TOTAL/b.SJ_MONTH11_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH11_FEE_TOTAL_PROP,
a.MONTH11_FEE_GAP,
--十二月
a.YS_MONTH12_FEE_TOTAL YS_MONTH12_FEE_TOTAL,
CASE WHEN b.YS_MONTH12_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.YS_MONTH12_FEE_TOTAL/b.YS_MONTH12_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' YS_MONTH12_FEE_TOTAL_PROP,
a.SJ_MONTH12_FEE_TOTAL SJ_MONTH12_FEE_TOTAL,
CASE WHEN b.SJ_MONTH12_FEE_TOTAL = 0 THEN '0.00' ELSE TO_CHAR(ROUND(a.SJ_MONTH12_FEE_TOTAL/b.SJ_MONTH12_FEE_TOTAL, 4) * 100, 'fm9999990.9999') END || '%' SJ_MONTH12_FEE_TOTAL_PROP,
a.MONTH12_FEE_GAP
FROM DETAIL a
INNER JOIN tt b ON 1 = 1
)
SELECT * FROM
(
SELECT ss.*, 4 seq
FROM ss
UNION ALL
SELECT
'合计' SUBJECT_LEVEL1,
SUM(YS_YEAR_FEE_TOTAL) YS_YEAR_FEE_TOTAL,
'100%' YS_YEAR_FEE_TOTAL_PROP,
SUM(SJ_YEAR_FEE_TOTAL) SJ_YEAR_FEE_TOTAL,
'100%' SJ_YEAR_FEE_TOTAL_PROP,
SUM(YEAR_FEE_GAP) YEAR_FEE_GAP,
--1月
SUM(YS_MONTH1_FEE_TOTAL) YS_MONTH1_FEE_TOTAL,
'100%' YS_MONTH1_FEE_TOTAL_PROP,
SUM(SJ_MONTH1_FEE_TOTAL) SJ_MONTH1_FEE_TOTAL,
'100%' SJ_MONTH1_FEE_TOTAL_PROP,
SUM(MONTH1_FEE_GAP) MONTH1_FEE_GAP,
--2月
SUM(YS_MONTH2_FEE_TOTAL) YS_MONTH2_FEE_TOTAL,
'100%' YS_MONTH2_FEE_TOTAL_PROP,
SUM(SJ_MONTH2_FEE_TOTAL) SJ_MONTH2_FEE_TOTAL,
'100%' SJ_MONTH2_FEE_TOTAL_PROP,
SUM(MONTH2_FEE_GAP) MONTH2_FEE_GAP,
--3月
SUM(YS_MONTH3_FEE_TOTAL) YS_MONTH3_FEE_TOTAL,
'100%' YS_MONTH3_FEE_TOTAL_PROP,
SUM(SJ_MONTH3_FEE_TOTAL) SJ_MONTH3_FEE_TOTAL,
'100%' SJ_MONTH3_FEE_TOTAL_PROP,
SUM(MONTH3_FEE_GAP) MONTH3_FEE_GAP,
--4月
SUM(YS_MONTH4_FEE_TOTAL) YS_MONTH4_FEE_TOTAL,
'100%' YS_MONTH4_FEE_TOTAL_PROP,
SUM(SJ_MONTH4_FEE_TOTAL) SJ_MONTH4_FEE_TOTAL,
'100%' SJ_MONTH4_FEE_TOTAL_PROP,
SUM(MONTH4_FEE_GAP) MONTH4_FEE_GAP,
--5月
SUM(YS_MONTH5_FEE_TOTAL) YS_MONTH5_FEE_TOTAL,
'100%' YS_MONTH5_FEE_TOTAL_PROP,
SUM(SJ_MONTH5_FEE_TOTAL) SJ_MONTH5_FEE_TOTAL,
'100%' SJ_MONTH5_FEE_TOTAL_PROP,
SUM(MONTH5_FEE_GAP) MONTH5_FEE_GAP,
--6月
SUM(YS_MONTH6_FEE_TOTAL) YS_MONTH6_FEE_TOTAL,
'100%' YS_MONTH6_FEE_TOTAL_PROP,
SUM(SJ_MONTH6_FEE_TOTAL) SJ_MONTH6_FEE_TOTAL,
'100%' SJ_MONTH6_FEE_TOTAL_PROP,
SUM(MONTH6_FEE_GAP) MONTH6_FEE_GAP,
--7月
SUM(YS_MONTH7_FEE_TOTAL) YS_MONTH7_FEE_TOTAL,
'100%' YS_MONTH7_FEE_TOTAL_PROP,
SUM(SJ_MONTH7_FEE_TOTAL) SJ_MONTH7_FEE_TOTAL,
'100%' SJ_MONTH7_FEE_TOTAL_PROP,
SUM(MONTH7_FEE_GAP) MONTH7_FEE_GAP,
--8月
SUM(YS_MONTH8_FEE_TOTAL) YS_MONTH8_FEE_TOTAL,
'100%' YS_MONTH8_FEE_TOTAL_PROP,
SUM(SJ_MONTH8_FEE_TOTAL) SJ_MONTH8_FEE_TOTAL,
'100%' SJ_MONTH8_FEE_TOTAL_PROP,
SUM(MONTH8_FEE_GAP) MONTH8_FEE_GAP,
--9月
SUM(YS_MONTH9_FEE_TOTAL) YS_MONTH9_FEE_TOTAL,
'100%' YS_MONTH9_FEE_TOTAL_PROP,
SUM(SJ_MONTH9_FEE_TOTAL) SJ_MONTH9_FEE_TOTAL,
'100%' SJ_MONTH9_FEE_TOTAL_PROP,
SUM(MONTH9_FEE_GAP) MONTH9_FEE_GAP,
--10月
SUM(YS_MONTH10_FEE_TOTAL) YS_MONTH10_FEE_TOTAL,
'100%' YS_MONTH10_FEE_TOTAL_PROP,
SUM(SJ_MONTH10_FEE_TOTAL) SJ_MONTH10_FEE_TOTAL,
'100%' SJ_MONTH10_FEE_TOTAL_PROP,
SUM(MONTH10_FEE_GAP) MONTH10_FEE_GAP,
--11月
SUM(YS_MONTH11_FEE_TOTAL) YS_MONTH11_FEE_TOTAL,
'100%' YS_MONTH11_FEE_TOTAL_PROP,
SUM(SJ_MONTH11_FEE_TOTAL) SJ_MONTH11_FEE_TOTAL,
'100%' SJ_MONTH11_FEE_TOTAL_PROP,
SUM(MONTH11_FEE_GAP) MONTH11_FEE_GAP,
--12月
SUM(YS_MONTH12_FEE_TOTAL) YS_MONTH12_FEE_TOTAL,
'100%' YS_MONTH12_FEE_TOTAL_PROP,
SUM(SJ_MONTH12_FEE_TOTAL) SJ_MONTH12_FEE_TOTAL,
'100%' SJ_MONTH12_FEE_TOTAL_PROP,
SUM(MONTH12_FEE_GAP) MONTH12_FEE_GAP,
16 seq
FROM ss
) nn
ORDER BY seq
总结
1、使用了with as 语句:
With…As()关键字,是以“With”关键字开头的Sql语句,在实际工作中,经常会遇到查询语句大部分相同的Sql语句,这时就可以将相同的Sql语句抽取出来,使用With…As定义,以便提高Sql的查询效率和代码的简洁。
针对一个别名
with tmp as (select * from tb_name)
针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
2、使用了union all 语句:
1)union all是将两个或者两个以上的搜索结果合并到一起但是不去重!这样可以节省时间,节约效率。因为去重需要对数据重新排列。
2)UNION也是将两个或者两个以上的搜索结果集合并在一起并且去掉重复的数据!这个合并是有条件的!记录的类型要匹配,记录的列数要一样!
3、使用内连接:
SELECT a.xxx,b.xxxx FROM DETAIL a INNER JOIN tt b ON 1 = 1
内连接:inner join on,on 1=1.表示恒成立,两表进行连接。