这是我的查询
Select
AgentActivityLog.StatusDateTime,
AgentActivityLog.UserId,
AgentActivityLog.StateDuration /60 AS Minutes,
AgentActivityLog.StatusKey
FROM UserWorkgroups
INNER JOIN AgentActivityLog ON UserWorkgroups.UserId = AgentActivityLog.UserId
WHERE
AgentActivityLog.StatusDateTime Between '08/01/2013' AND '08/02/2013' AND
StatusKey IN ('Shadow Trainer') AND
(UserWorkgroups.WorkGroup in ('LV_GR_9','LV_CCS_GENERAL','LV_PBX_INTERNAL'))
ORDER BY
AgentActivityLog.StatusDateTime,
AgentActivityLog.UserId,
AgentActivityLog.StatusKey,
AgentActivityLog.StateDuration
结果
2013-08-01 08:59:03.000 JMENDOZA1 13 Shadow Trainer
2013-08-01 09:13:50.000 JMENDOZA1 12 Shadow Trainer
我希望结果总结当天的StateDuration
这样的事情
2013-08-01 08:59:03.000 JMENDOZA1 25 Shadow Trainer.
感谢您的帮助.
解决方法:
使用GROUP BY UserId,因此将具有相同用户ID的行分组.在SELECT列表的其余列中,必须应用聚合函数.你想要StatusDuration的SUM()和StatusDateTime的MIN()或MAX()(或两者!).
您还可以使用别名来提高可读性:
SELECT
MIN(aal.StatusDateTime) AS MinStatusDateTime,
MAX(aal.StatusDateTime) AS MaxStatusDateTime,
aal.UserId,
SUM(aal.StateDuration / 60) AS Minutes,
'Shadow Trainer' AS StatusKey
FROM UserWorkgroups AS uw
INNER JOIN AgentActivityLog AS aal ON uw.UserId = aal.UserId
WHERE
aal.StatusDateTime BETWEEN '2013-08-01' AND '2013-08-02' AND
aal.StatusKey IN ('Shadow Trainer') AND
uw.WorkGroup IN ('LV_GR_9','LV_CCS_GENERAL','LV_PBX_INTERNAL')
GROUP BY
aal.UserId
ORDER BY
MinStatusDateTime,
UserId,
StatusKey,
Minutes ;
标签:mysql,sql
来源: https://codeday.me/bug/20190709/1408206.html