如何用单条SQL语句展示故障最多的计算机的所有故障详情

可以通过两种常见方式实现单条SQL语句获取故障最多的计算机的所有故障详情：

方法1：子查询定位故障最多的计算机 #

先通过嵌套子查询找到故障次数的最大值，再筛选出对应计算机的所有工单记录：

SELECT ID_TICKET, SERIAL_NO_COMPUTER, PROBLEM_DESCRIPTION
FROM TICKET
WHERE SERIAL_NO_COMPUTER IN (
    SELECT SERIAL_NO_COMPUTER
    FROM TICKET
    GROUP BY SERIAL_NO_COMPUTER
    HAVING COUNT(*) = (
        SELECT MAX(occurances)
        FROM (
            SELECT COUNT(*) AS occurances
            FROM TICKET
            GROUP BY SERIAL_NO_COMPUTER
        ) AS count_sub
    )
);

说明 #

• 最内层子查询统计每台计算机的故障数，外层子查询提取最大故障次数
• 最外层查询筛选出故障次数等于最大值的计算机的所有故障详情，支持多台计算机故障数并列最多的场景

方法2：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

用RANK()窗口函数为计算机的故障数排名，直接筛选排名第一的计算机的所有记录：

WITH computer_fault_ranks AS (
    SELECT 
        SERIAL_NO_COMPUTER,
        COUNT(*) AS occurances,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rank
    FROM TICKET
    GROUP BY SERIAL_NO_COMPUTER
)
SELECT t.ID_TICKET, t.SERIAL_NO_COMPUTER, t.PROBLEM_DESCRIPTION
FROM TICKET t
JOIN computer_fault_ranks c ON t.SERIAL_NO_COMPUTER = c.SERIAL_NO_COMPUTER
WHERE c.rank = 1;

说明 #

• 先通过CTE统计每台计算机的故障数并排名，再关联原表获取对应故障详情
• RANK()会保留并列排名，若只想取单台故障最多的计算机，可改用ROW_NUMBER()

基于你的初始语句修改 #

如果只需要取故障最多的一台计算机（不考虑并列），可以直接复用你的统计语句：

SELECT t.ID_TICKET, t.SERIAL_NO_COMPUTER, t.PROBLEM_DESCRIPTION
FROM TICKET t
JOIN (
    SELECT SERIAL_NO_COMPUTER, COUNT(*) AS OCCURANCES
    FROM TICKET 
    GROUP BY SERIAL_NO_COMPUTER 
    ORDER BY COUNT(*) DESC
    LIMIT 1 -- SQL Server用TOP 1，Oracle用FETCH FIRST 1 ROW ONLY
) AS top_comp ON t.SERIAL_NO_COMPUTER = top_comp.SERIAL_NO_COMPUTER;

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