mysql 按每小时分组统计（行转列）

叁歲伎倆 2022-01-14 05:39 589阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

SELECT PRODUCT_NAME productName, PRODUCT_ID productId,${parameter.queryTime} timeFlag,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'09') THEN AMOUNT ELSE 0 END) AS amount9,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'10') THEN AMOUNT ELSE 0 END) AS amount10,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'11') THEN AMOUNT ELSE 0 END) AS amount11,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'12') THEN AMOUNT ELSE 0 END) AS amount12,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'13') THEN AMOUNT ELSE 0 END) AS amount13,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'14') THEN AMOUNT ELSE 0 END) AS amount14,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'15') THEN AMOUNT ELSE 0 END) AS amount15,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'16') THEN AMOUNT ELSE 0 END) AS amount16,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'17') THEN AMOUNT ELSE 0 END) AS amount17,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'18') THEN AMOUNT ELSE 0 END) AS amount18,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'19') THEN AMOUNT ELSE 0 END) AS amount19,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'20') THEN AMOUNT ELSE 0 END) AS amount20,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'21') THEN AMOUNT ELSE 0 END) AS amount21,
                MAX(CASE REPORT_FLAG WHEN CONCAT(#{parameter.queryTime},'22') THEN AMOUNT ELSE 0 END) AS amount22
            FROM
                S_REPORT_EB_WICKET o
            WHERE o.REPORT_FLAG>=CONCAT(#{parameter.queryTime},'09') AND o.REPORT_FLAG <= CONCAT(#{parameter.queryTime},'22')

![171418_yICY_615096.png][]

转载于:https://my.oschina.net/stonezing/blog/680586

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/aea0a35730cb4a4c844f1c879161ac13.png
[171418_yICY_615096.png]: /images/20220114/6167b2bfcd044478934b62269d40c376.png