我需要mysql按查询分组，以便为每个雇员在一行中的每个雇员返回每小时一次的日期时间列的条目。

Question

我的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来呈现报告。

Answer 1

你不需要GROUP BY ...,HOUR(datenew)，而只需要person

http://sqlfiddle.com/#!9/b93760/1

SELECT person,
SUM(HOUR(datenew)=9) AS `9-10`,
SUM(HOUR(datenew)=10) AS `10-11`,
SUM(HOUR(datenew)=11)  AS `11-12`,
SUM(HOUR(datenew)=12)  AS `12-13`,
COUNT(*) 
FROM mydatatable 
WHERE mydate = '2018-01-18' 
GROUP BY person

Answer 2

使用派生表，应该是

select 
x.person,
sum(`9-10`) as `9-10`,
.
.
.
.
.
.
.
.
 from 
(
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) 
) x

group by x.person

Answer 3

您可以使用一个(假的)聚合函数例如: min()对一行中的行分组

SELECT person,
  min(IF((HOUR(datenew))= 9, COUNT(id),'')) AS `9-10`,
  min(IF((HOUR(datenew)) = 10, COUNT(id),'')) AS `10-11`,
  min(IF((HOUR(datenew)) = 11, COUNT(id),'')) AS `11-12`,
  min(IF((HOUR(datenew)) = 12, COUNT(id),'')) AS `12-13`,
  min(IF((HOUR(datenew)) = 13, COUNT(id),'')) AS `13-14`,
  min(IF((HOUR(datenew)) = 14, COUNT(id),'')) AS `14-15`,
  min(IF((HOUR(datenew)) = 15, COUNT(id),'')) AS `15-16`,
  min(IF((HOUR(datenew)) = 16, COUNT(id),'')) AS `16-17`,
  min(IF((HOUR(datenew)) = 17, COUNT(id),'')) AS `17-18`,
  min(IF((HOUR(datenew)) = 18, COUNT(id),'')) AS `18-19`,
  min(IF((HOUR(datenew)) = 19, COUNT(id),'')) AS `19-20`,
  min(IF((HOUR(datenew)) = 20, COUNT(id),'')) AS `20-21`,
  min(IF((HOUR(datenew)) = 21, COUNT(id),'')) AS `21-22`,
COUNT(*) FROM mydatatable 
WHERE mydate = '2018-01-18' 
GROUP BY person