mysql - 为什么此GROUP BY Mysql查询不起作用？

SELECT time.EmpID, time.Date,
SUM(MinutesatState) AS active FROM time WHERE State = 'active'
GROUP BY time.EmpID, time.Date;

以上查询工作

SELECT time.EmpID, time.Date,
SUM(MinutesatState) AS idle FROM time WHERE State = 'idle'
GROUP BY time.EmpID, time.Date;

以上查询也有效
但是当我在同一个语句中运行这两个查询时，它不起作用

SELECT time.EmpID, time.Date,
SUM(MinutesatState) AS active FROM time WHERE State = 'active'
SUM(MinutesatState) AS idle FROM time WHERE State = 'idle'
GROUP BY time.EmpID, time.Date;

最佳答案

你上一个查询语法错误。不能像那样使用FROM子句。您正在使用CASE..WHEN子句查找条件聚合。

SELECT
  EmpID,
  Date,
  SUM(CASE WHEN State = 'active' THEN MinutesatState ELSE 0 END) AS active,
  SUM(CASE WHEN State = 'idle' THEN MinutesatState ELSE 0 END) AS idle
FROM time
GROUP BY EmpID, Date;

关于mysql - 为什么此GROUP BY Mysql查询不起作用？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53494174/