SELECT CREATETION AS “年份”
,SUM(DATE_ROW01) AS “01月”
,SUM(DATE_ROW02) AS “02月”
,SUM(DATE_ROW03) AS “03月”
,SUM(DATE_ROW04) AS “04月”
,SUM(DATE_ROW05) AS “05月”
,SUM(DATE_ROW06) AS “06月”
,SUM(DATE_ROW07) AS “07月”
,SUM(DATE_ROW08) AS “08月”
,SUM(DATE_ROW09) AS “09月”
,SUM(DATE_ROW10) AS “10月”
,SUM(DATE_ROW11) AS “11月”
,SUM(DATE_ROW12) AS “12月”
FROM
(
SELECT CREATETION
,NVL(CASE
WHEN MONTH = ’01’ THEN SUM(COMMISSION) END,0) AS DATE_ROW01
,NVL(CASE
WHEN MONTH = ’02’ THEN SUM(COMMISSION) END,0) AS DATE_ROW02
,NVL(CASE
WHEN MONTH = ’03’ THEN SUM(COMMISSION) END,0) AS DATE_ROW03
,NVL(CASE
WHEN MONTH = ’04’ THEN SUM(COMMISSION) END,0) AS DATE_ROW04
,NVL(CASE
WHEN MONTH = ’05’ THEN SUM(COMMISSION) END,0) AS DATE_ROW05
,NVL(CASE
WHEN MONTH = ’06’ THEN SUM(COMMISSION) END,0) AS DATE_ROW06
,NVL(CASE
WHEN MONTH = ’07’ THEN SUM(COMMISSION) END,0) AS DATE_ROW07
,NVL(CASE
WHEN MONTH = ’08’ THEN SUM(COMMISSION) END,0) AS DATE_ROW08
,NVL(CASE
WHEN MONTH = ’09’ THEN SUM(COMMISSION) END,0) AS DATE_ROW09
,NVL(CASE
WHEN MONTH = ’10’ THEN SUM(COMMISSION) END,0) AS DATE_ROW10
,NVL(CASE
WHEN MONTH = ’11’ THEN SUM(COMMISSION) END,0) AS DATE_ROW11
,NVL(CASE
WHEN MONTH = ’12’ THEN SUM(COMMISSION) END,0) AS DATE_ROW12
FROM (
SELECT TO_CHAR(CREATE_TIME,’YYYY’) AS CREATETION
,TO_CHAR(CREATE_TIME,’MM’) AS MONTH
,COMMISSION,ROW_NUMBER() OVER(PARTITION BY to_char(CREATE_TIME,’YYYY’) ORDER BY TO_CHAR(CREATE_TIME,’MM’) ) AS RN
FROM TEST_LYH
)
GROUP BY CREATETION,MONTH
)
GROUP BY CREATETION
ORDER BY CREATETION
取消
评论
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/213902.html原文链接：https://javaforall.net
