为什么这个groupbymysql查询不起作用？

czfnxgou 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(354)

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;

mysql

来源：https://stackoverflow.com/questions/53494174/why-doesnt-this-group-by-mysql-query-work

2条答案

按热度按时间

gab6jxml1#

可以将状态包含在GROUPBY列中。

SELECT time.EmpID, time.Date,State,
SUM(MinutesatState) As SumOfMinutes FROM time GROUP BY time.EmpID, time.Date,State;

赞(0）回复(0）举报 2021-06-18

kgsdhlau2#

上一个查询语法错误。你不能使用 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;

赞(0）回复(0）举报 2021-06-17

我来回答

为什么这个groupbymysql查询不起作用？

2条答案

相关问题

热门标签

最新问答