如何对指定MySQL时间日志查询结果按UTL_ACTION分组并求和？

要实现对原查询结果按UTL_ACTION分组，并对每组的UTL_DURATION求和，你可以把原查询的结果作为一个数据源，在外层执行分组聚合操作。下面提供两种常用的实现方式：

方式一：使用子查询 #

把你的原查询包裹成一个子查询，然后在外层通过GROUP BY UTL_ACTION分组，并用SUM(UTL_DURATION)计算每组的总时长：

SELECT 
    t.UTL_ACTION,
    SUM(t.UTL_DURATION) AS TOTAL_DURATION -- 求和后的总时长，单位秒
FROM (
    -- 这里是你原有的查询语句
    SELECT 
        A.PK_USER_TIME_LOG_ID, 
        A.CLIENT_ID, 
        A.PROJECT_ID, 
        A.USER_ID, 
        A.UTL_DTSTAMP, 
        -- DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, 
        A.UTL_LATITUDE, 
        A.UTL_LONGITUDE, 
        A.UTL_EVENT, 
        A.UTL_ACTION, 
        TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION 
    FROM tbl_user_time_log A 
    INNER JOIN tbl_user_time_log B ON B.PK_USER_TIME_LOG_ID = (A.PK_USER_TIME_LOG_ID + 1) 
    WHERE 
        A.USER_ID = '465605' 
        AND (A.UTL_DTSTAMP BETWEEN '2018-01-22' AND '2018-01-28') 
        AND A.UTL_EVENT <> 'CLOCK OUT' 
    ORDER BY A.PK_USER_TIME_LOG_ID ASC
) t
GROUP BY t.UTL_ACTION
ORDER BY t.UTL_ACTION; -- 可选：按UTL_ACTION排序结果

方式二：使用CTE（公用表表达式，MySQL 8.0+支持） #

如果你的MySQL版本是8.0及以上，用CTE会让代码更易读，逻辑更清晰：

WITH time_log_data AS (
    -- 原查询内容
    SELECT 
        A.PK_USER_TIME_LOG_ID, 
        A.CLIENT_ID, 
        A.PROJECT_ID, 
        A.USER_ID, 
        A.UTL_DTSTAMP, 
        -- DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, 
        A.UTL_LATITUDE, 
        A.UTL_LONGITUDE, 
        A.UTL_EVENT, 
        A.UTL_ACTION, 
        TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION 
    FROM tbl_user_time_log A 
    INNER JOIN tbl_user_time_log B ON B.PK_USER_TIME_LOG_ID = (A.PK_USER_TIME_LOG_ID + 1) 
    WHERE 
        A.USER_ID = '465605' 
        AND (A.UTL_DTSTAMP BETWEEN '2018-01-22' AND '2018-01-28') 
        AND A.UTL_EVENT <> 'CLOCK OUT' 
    ORDER BY A.PK_USER_TIME_LOG_ID ASC
)
SELECT 
    UTL_ACTION,
    SUM(UTL_DURATION) AS TOTAL_DURATION
FROM time_log_data
GROUP BY UTL_ACTION
ORDER BY UTL_ACTION;

小提示 #

• 如果UTL_DURATION可能出现NULL值（比如最后一条记录没有对应的下一条B记录），可以用SUM(COALESCE(UTL_DURATION, 0))来避免求和结果为NULL。
• 原查询中的ORDER BY在子查询/CTE里其实可以去掉，因为外层分组后排序会重新生效，不过保留也不影响最终结果。

内容的提问来源于stack exchange，提问作者suchislife801