如何编写SQL查询按状态统计重复名称并生成带总计的透视表

背景操作 #

1. 查找重复名称的SQL：

SELECT Name, COUNT(*) 
FROM Tab 
GROUP BY Name
HAVING COUNT(*) > 1
ORDER BY 2 DESC, 1;

查询结果示例：

2. 按状态统计重复名称总记录数的SQL：

SELECT Status, COUNT(*) FROM Tab
WHERE Name IN (SELECT Name FROM Tab GROUP BY Name HAVING COUNT(*) > 1)
GROUP BY Status
ORDER by Name;

查询结果：

目标结果 #

需要生成每个重复名称在各状态下的计数，同时添加Total总计列，格式如下：

实现SQL语句 #

SELECT 
    t.Name,
    COALESCE(SUM(CASE WHEN t.Status = 'Ended' THEN 1 ELSE 0 END), 0) AS Ended,
    COALESCE(SUM(CASE WHEN t.Status = 'Deleted' THEN 1 ELSE 0 END), 0) AS Deleted,
    COALESCE(SUM(CASE WHEN t.Status = 'InUse' THEN 1 ELSE 0 END), 0) AS InUse,
    COUNT(*) AS Total
FROM Tab t
WHERE t.Name IN (
    SELECT Name 
    FROM Tab 
    GROUP BY Name 
    HAVING COUNT(*) > 1
)
GROUP BY t.Name
ORDER BY Total DESC, t.Name;

语句说明 #

• 用CASE配合SUM实现状态数据的行转列统计，COALESCE保证无对应状态时显示0而非NULL；
• COUNT(*)直接计算每个名称的总记录数，生成Total列；
• 子查询筛选出存在重复的名称，仅统计这类名称的数据；
• 最终按Total降序、Name升序排序，和最初的重复名称查询排序逻辑保持一致。

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