SQL如何统计每个工单操作人对应工单最高状态的数量

问题原因 #

你写的SQL存在两个核心问题：

1. 未按规则筛选每个工单的最高状态记录：直接全表分组统计会把同一个工单的所有历史状态变更记录都计入统计，不符合「取每个ticket_id对应最高状态值」的要求
2. 字段名拼写错误：原表字段为Author of Changes，你写的autor_of_changes缺少字母h，带空格的字段名需要用对应数据库的标识符包裹（比如MySQL用反引号，SQL Server用方括号）

解决方案 #

通用方案（支持MySQL8+、PostgreSQL、SQL Server等所有支持窗口函数的数据库） #

先通过窗口函数筛选每个工单最高状态对应的有效记录，再分组统计：

-- 第一步：筛选每个工单最高状态的记录
WITH ticket_max_status AS (
    SELECT 
        Ticket_ID,
        `Author of Changes`,
        `Ticket Status`,
        -- 同一工单按状态降序排序，状态相同按时间降序取最新记录
        ROW_NUMBER() OVER (PARTITION BY Ticket_ID ORDER BY `Ticket Status` DESC, time_stamp DESC) AS rn
    FROM aa_ticketing_app_log
)
-- 第二步：统计每个操作人各状态的工单数量
SELECT 
    `Author of Changes` AS 操作人,
    `Ticket Status` AS 工单状态,
    COUNT(Ticket_ID) AS 工单数
FROM ticket_max_status
WHERE rn = 1 -- 只取每个工单最高状态的记录
GROUP BY `Author of Changes`, `Ticket Status`
ORDER BY 操作人, 工单状态;

行转列展示版本（直接展示每个操作人各状态的工单数） #

如果需要把四种状态作为列展示，更直观查看结果，可以用条件聚合：

WITH ticket_max_status AS (
    SELECT 
        Ticket_ID,
        `Author of Changes`,
        `Ticket Status`,
        ROW_NUMBER() OVER (PARTITION BY Ticket_ID ORDER BY `Ticket Status` DESC, time_stamp DESC) AS rn
    FROM aa_ticketing_app_log
)
SELECT 
    `Author of Changes` AS 操作人,
    SUM(CASE WHEN `Ticket Status` = 0 THEN 1 ELSE 0 END) AS 提交工单数,
    SUM(CASE WHEN `Ticket Status` = 1 THEN 1 ELSE 0 END) AS 受理工单数,
    SUM(CASE WHEN `Ticket Status` = 2 THEN 1 ELSE 0 END) AS 办结工单数,
    SUM(CASE WHEN `Ticket Status` = 5 THEN 1 ELSE 0 END) AS 驳回工单数,
    COUNT(Ticket_ID) AS 总负责工单数
FROM ticket_max_status
WHERE rn = 1
GROUP BY `Author of Changes`
ORDER BY 总负责工单数 DESC;

低版本MySQL兼容方案（不支持CTE和窗口函数的场景） #

如果用的是MySQL5.x及以下不支持窗口函数的版本，可以用子查询关联的方式实现：

SELECT 
    a.`Author of Changes` AS 操作人,
    a.`Ticket Status` AS 工单状态,
    COUNT(a.Ticket_ID) AS 工单数
FROM aa_ticketing_app_log a
INNER JOIN (
    -- 先查询每个工单的最高状态
    SELECT Ticket_ID, MAX(`Ticket Status`) AS max_status
    FROM aa_ticketing_app_log
    GROUP BY Ticket_ID
) b ON a.Ticket_ID = b.Ticket_ID AND a.`Ticket Status` = b.max_status
-- 处理同一工单多个同最高状态的记录，取时间最新的那条
INNER JOIN (
    SELECT Ticket_ID, `Ticket Status`, MAX(time_stamp) AS max_time
    FROM aa_ticketing_app_log
    GROUP BY Ticket_ID, `Ticket Status`
) c ON a.Ticket_ID = c.Ticket_ID AND a.`Ticket Status` = c.`Ticket Status` AND a.time_stamp = c.max_time
GROUP BY a.`Author of Changes`, a.`Ticket Status`
ORDER BY 操作人, 工单状态;

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