在MySQL中创建一个透视表/水平视图并导出到PHP

Question

我使用此查询生成小时报告：

查询：

SELECT DATE(IST_In_Time) date,HOUR(IST_In_Time) hour,COUNT(*) count FROM spillreport WHERE DATE(IST_In_Time)>= '2014-07-22 00:00:00' AND DATE(IST_In_Time)<= '2014-07-26 00:00:00' GROUP BY HOUR(IST_In_Time), DATE(IST_In_Time) ORDER BY DATE(IST_In_Time)ASC

结果：

date    hour    count
2014-07-22  19  1
2014-07-22  14  1
2014-07-23  18  28
2014-07-23  15  1
2014-07-23  19  26
2014-07-23  17  1
2014-07-23  20  8
2014-07-24  11  34
2014-07-24  19  2
2014-07-24  8   1
2014-07-24  12  35
2014-07-24  13  23
2014-07-24  15  37
2014-07-24  14  52
2014-07-24  10  34
2014-07-24  16  59
2014-07-24  9   15
2014-07-24  17  46
2014-07-24  18  25
2014-07-25  8   1
2014-07-26  19  1
2014-07-26  8   2

我想分组唯一的日期和水平视图：

 Hours 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23    
2014-07-22 - - - - - - - - - - -  -   -  -  1  -  -  -  -  1  -  -  -  -
2014-07-23 - - - - - - - - - - -  -   -  -  -  1  - 1 28  26  8  -  -  -
2014-07-24
2014-07-25
2014-07-26

我知道这是通过创建视图来完成的。。

我同样需要帮助！

Answer 1

SELECT DATE(IST_In_Time) date,
       sum(case when HOUR(IST_In_Time) = 1 then 1 else null end) as h01,
       sum(case when HOUR(IST_In_Time) = 2 then 1 else null end) as h02,
       sum(case when HOUR(IST_In_Time) = 3 then 1 else null end) as h03,
       sum(case when HOUR(IST_In_Time) = 4 then 1 else null end) as h04,
       sum(case when HOUR(IST_In_Time) = 5 then 1 else null end) as h05,
       sum(case when HOUR(IST_In_Time) = 6 then 1 else null end) as h06,
       sum(case when HOUR(IST_In_Time) = 7 then 1 else null end) as h07,
       sum(case when HOUR(IST_In_Time) = 8 then 1 else null end) as h08,
       sum(case when HOUR(IST_In_Time) = 9 then 1 else null end) as h09,
       sum(case when HOUR(IST_In_Time) = 10 then 1 else null end) as h10,
       sum(case when HOUR(IST_In_Time) = 11 then 1 else null end) as h11,
       sum(case when HOUR(IST_In_Time) = 12 then 1 else null end) as h12,
       sum(case when HOUR(IST_In_Time) = 13 then 1 else null end) as h13,
       sum(case when HOUR(IST_In_Time) = 14 then 1 else null end) as h14,
       sum(case when HOUR(IST_In_Time) = 15 then 1 else null end) as h15,
       sum(case when HOUR(IST_In_Time) = 16 then 1 else null end) as h16,
       sum(case when HOUR(IST_In_Time) = 17 then 1 else null end) as h17,
       sum(case when HOUR(IST_In_Time) = 18 then 1 else null end) as h18,
       sum(case when HOUR(IST_In_Time) = 19 then 1 else null end) as h19,
       sum(case when HOUR(IST_In_Time) = 20 then 1 else null end) as h20,
       sum(case when HOUR(IST_In_Time) = 21 then 1 else null end) as h21,
       sum(case when HOUR(IST_In_Time) = 22 then 1 else null end) as h22,
       sum(case when HOUR(IST_In_Time) = 23 then 1 else null end) as h23
  FROM spillreport
 WHERE DATE(IST_In_Time) >= '2014-07-22 00:00:00'
   AND DATE(IST_In_Time) <= '2014-07-26 00:00:00'
 GROUP BY DATE(IST_In_Time)
 ORDER BY DATE(IST_In_Time) ASC

使用带有CASE语句的条件聚合