使用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
;
效果如下: