MySQL列转行

使用case when达到列转行的效果，如下：
1.为了统计摄像头一周产生的数量，将摄像头放在纵轴，将时间放在横轴。
2.先按摄像头和时间分组统计数量合并起来，再在外层再按摄像头分组。使用MAX函数是为了只用摄像头分组，否则会出现一个摄像头有7行。
3.为了更好看后面日期的情况，按倒序排序。

SELECT  sxtid
        ,MAX(CASE WHEN dt='20190101' THEN nums END ) AS dt_20190101
        ,MAX(CASE WHEN dt='20190102' THEN nums END ) AS dt_20190102
        ,MAX(CASE WHEN dt='20190103' THEN nums END ) AS dt_20190103
        ,MAX(CASE WHEN dt='20190104' THEN nums END ) AS dt_20190104
        ,MAX(CASE WHEN dt='20190105' THEN nums END ) AS dt_20190105
        ,MAX(CASE WHEN dt='20190106' THEN nums END ) AS dt_20190106
        ,MAX(CASE WHEN dt='20190107' THEN nums END ) AS dt_20190107
FROM    (
            SELECT  sxtid
                    ,dt
                    ,COUNT(sxtid) AS nums
            FROM    table1
            WHERE   dt BETWEEN '20190101' AND '20190107'
            GROUP BY sxtid,dt
            UNION ALL
            SELECT  sxtid
                    ,dt
                    ,COUNT(sxtid) AS nums
            FROM    table2
            WHERE   dt BETWEEN '20190101' AND '20190107'
            GROUP BY sxtid,dt
            UNION ALL
            SELECT  sxtid
                    ,dt
                    ,COUNT(sxtid) AS nums
            FROM    table3
            WHERE   dt BETWEEN '20190101' AND '20190107'
            GROUP BY sxtid,dt
            UNION ALL
            SELECT  sxtid
                    ,dt
                    ,COUNT(sxtid) AS nums
            FROM    table4
            WHERE   dt BETWEEN '20190101' AND '20190107'
            GROUP BY sxtid,dt
        ) a
GROUP BY sxtid
ORDER BY dt_20190107,dt_20190106,dt_20190105,dt_20190104,dt_20190103,dt_20190102,dt_20190101
;

效果如下：