mysql - 如果两行相等，如何求和？

这是我的查询

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 列表的其余列中，必须应用聚合函数。您需要 SUM() 的 StateDuration 和 MIN() 或 MAX() (或两者!)的 StatusDateTime 。

您还可以使用别名来提高可读性:

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 - 如果两行相等，如何求和？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18755702/