mysql行转列之sum

来源：互联网发布：河北云狐网络编辑：程序博客网时间：2024/06/06 01:11

SELECT
AD_CODE,
SUM(DECODE(YEAR,'2016'/*年度变量+1*/ ,CD_AMT,0))/10000 NO1CD_AMT,
SUM(DECODE(YEAR,'2016'/*年度变量+1*/ ,CDBJ_AMT,0))/10000 NO1CDBJ_AMT,
SUM(DECODE(YEAR,'2016'/*年度变量+1*/ ,CDLX_AMT,0))/10000 NO1CDLX_AMT,
SUM(DECODE(YEAR,'2019'/*年度变量+2*/ ,CD_AMT,0))/10000 NO2CD_AMT,
SUM(DECODE(YEAR,'2019'/*年度变量+2*/ ,CDBJ_AMT,0))/10000 NO2CDBJ_AMT,
SUM(DECODE(YEAR,'2019'/*年度变量+2*/ ,CDLX_AMT,0))/10000 NO2CDLX_AMT,
SUM(DECODE(YEAR,'2020'/*年度变量+3*/ ,CD_AMT,0))/10000 NO3CD_AMT,
SUM(DECODE(YEAR,'2020'/*年度变量+3*/ ,CDBJ_AMT,0))/10000 NO3CDBJ_AMT,
SUM(DECODE(YEAR,'2020'/*年度变量+3*/ ,CDLX_AMT,0))/10000 NO3CDLX_AMT,
SUM(DECODE(YEAR,'2021'/*年度变量+4*/ ,CD_AMT,0))/10000 NO4CD_AMT,
SUM(DECODE(YEAR,'2021'/*年度变量+4*/ ,CDBJ_AMT,0))/10000 NO4CDBJ_AMT,
SUM(DECODE(YEAR,'2021'/*年度变量+4*/ ,CDLX_AMT,0))/10000 NO4CDLX_AMT,
SUM(DECODE(YEAR,'2022'/*年度变量+5*/ ,CD_AMT,0))/10000 NO5CD_AMT,
SUM(DECODE(YEAR,'2022'/*年度变量+5*/ ,CDBJ_AMT,0))/10000 NO5CDBJ_AMT,
SUM(DECODE(YEAR,'2022'/*年度变量+5*/ ,CDLX_AMT,0))/10000 NO5CDLX_AMT
FROM
(SELECT SUM(CD_AMT) CD_AMT,
SUM(DECODE(T.PLAN_TYPE, 0, T.CD_AMT, 0)) CDBJ_AMT,
SUM(DECODE(T.PLAN_TYPE, 1, T.CD_AMT, 0)) CDLX_AMT,
SUBSTR(T.DF_END_DATE, 1, 4) YEAR,
T.AD_CODE
FROM DEBT_T_ZQGL_DFJH T
WHERE SUBSTR(T.DF_END_DATE, 1, 4) >= '2016'/*年度变量+1*/
AND SUBSTR(T.DF_END_DATE, 1, 4) <= '2022'/*年度变量+5*/
GROUP BY SUBSTR(T.DF_END_DATE, 1, 4),T.AD_CODE
) GROUP BY AD_CODE
ORDER BY AD_CODE

阅读全文

0 0