我的mysql查询:
SELECT person,
IF((HOUR(datenew))= 9, COUNT(id),'') AS `9-10`,
IF((HOUR(datenew)) = 10, COUNT(id),'') AS `10-11`,
IF((HOUR(datenew)) = 11, COUNT(id),'') AS `11-12`,
IF((HOUR(datenew)) = 12, COUNT(id),'') AS `12-13`,
IF((HOUR(datenew)) = 13, COUNT(id),'') AS `13-14`,
IF((HOUR(datenew)) = 14, COUNT(id),'') AS `14-15`,
IF((HOUR(datenew)) = 15, COUNT(id),'') AS `15-16`,
IF((HOUR(datenew)) = 16, COUNT(id),'') AS `16-17`,
IF((HOUR(datenew)) = 17, COUNT(id),'') AS `17-18`,
IF((HOUR(datenew)) = 18, COUNT(id),'') AS `18-19`,
IF((HOUR(datenew)) = 19, COUNT(id),'') AS `19-20`,
IF((HOUR(datenew)) = 20, COUNT(id),'') AS `20-21`,
IF((HOUR(datenew)) = 21, COUNT(id),'') AS `21-22`,
COUNT(*) FROM mydatatable WHERE mydate = '2018-01-18' GROUP BY person,HOUR(datenew)
我当前的查询输出:
我想要的输出:
我将使用php呈现报告。
3条答案
按热度按时间rjee0c151#
你不需要这么做
GROUP BY ...,HOUR(datenew)
但只是person
http://sqlfiddle.com/#!9/b93760/1号368yc8dk2#
使用派生表时,应
qco9c6ql3#
可以使用(假)聚合函数,例如:min()将行分组到一行中