MySQL：筛选无accepted记录的分组报表编码的高效查询方案

数据表结构与数据 #

现有数据表（假设表名为reports）如下：

需求 #

1. 从report_code中提取第4位开始的5位编码（使用SUBSTR(report_code,4,5)函数，不同数据库可能有函数名差异，比如MySQL用SUBSTRING）
2. 筛选出该编码对应的所有记录中完全没有accepted状态的编码
3. 分组输出，同一编码仅显示一次
4. 替代低效的循环嵌套查询，实现高效执行

高效SQL解法 #

方法1：GROUP BY + HAVING（推荐，逻辑简洁且性能优异）

这是最直接的实现方式，通过分组后聚合判断每组是否存在accepted记录：

SELECT SUBSTR(report_code, 4, 5) AS group_code
FROM reports
GROUP BY SUBSTR(report_code, 4, 5)
HAVING SUM(CASE WHEN result_of_report = 'accepted' THEN 1 ELSE 0 END) = 0;

或者用MAX函数判断：

SELECT SUBSTR(report_code, 4, 5) AS group_code
FROM reports
GROUP BY SUBSTR(report_code, 4, 5)
HAVING MAX(CASE WHEN result_of_report = 'accepted' THEN 1 ELSE 0 END) = 0;

原理：分组后统计每组中accepted的数量（或判断是否存在），数量为0则说明该分组无accepted记录。

方法2：NOT EXISTS（适合有索引优化的场景）

如果数据表在report_code和result_of_report上有联合索引，这种方式的性能会非常出色：

SELECT DISTINCT SUBSTR(report_code, 4, 5) AS group_code
FROM reports t1
WHERE NOT EXISTS (
    SELECT 1
    FROM reports t2
    WHERE SUBSTR(t2.report_code, 4, 5) = SUBSTR(t1.report_code, 4, 5)
      AND t2.result_of_report = 'accepted'
);

原理：通过子查询排除所有存在accepted记录的分组，DISTINCT保证编码不重复输出。

方法3：窗口函数（适合需扩展分组信息的场景）

如果需要同时获取分组的其他状态信息，可以用窗口函数预先计算分组是否存在accepted：

WITH group_status AS (
    SELECT 
        SUBSTR(report_code, 4, 5) AS group_code,
        MAX(CASE WHEN result_of_report = 'accepted' THEN 1 ELSE 0 END) OVER (PARTITION BY SUBSTR(report_code, 4, 5)) AS has_accepted
    FROM reports
)
SELECT DISTINCT group_code
FROM group_status
WHERE has_accepted = 0;

原理：先通过窗口函数为每条记录标记所属分组是否有accepted，再筛选出标记为0的分组并去重。

性能优化建议 #

• 建立函数索引：如果数据库支持（如Oracle、MySQL 8.0+），可以为SUBSTR(report_code,4,5)建立函数索引，大幅提升分组查询效率。
• 联合索引：为report_code和result_of_report建立联合索引，能加速NOT EXISTS子查询的执行。
• 优先选择GROUP BY + HAVING：大多数数据库的查询优化器对这种写法的支持最好，执行计划通常更高效。

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