如何编写SQL查询筛选Subjects表中满足聚合条件且无Courses关联的记录？

没问题，我来帮你搞定这个查询！你需要的是从Subjects表中筛选出科目名称重复出现且在Courses表中无对应记录的条目，咱们一步步来实现：

核心需求拆解 #

你需要同时满足两个条件：

• 该记录的SUBJECT_NAME在Subjects表中出现过多次
• 该记录的ID不存在于Courses表的SUBJECT_ID字段中

方案一：使用子查询+聚合函数 #

你已经写了排除Courses对应条目的部分，现在只需要加入一个子查询来找出重复的科目名称即可：

SELECT s.*
FROM subjects s
WHERE 
    -- 排除在Courses表中有对应条目的记录
    s.id NOT IN (SELECT subject_id FROM courses)
    -- 筛选出科目名称重复出现的记录
    AND s.subject_name IN (
        SELECT subject_name
        FROM subjects
        GROUP BY subject_name
        HAVING COUNT(*) > 1
    );

语句解释：

• 内层的子查询SELECT subject_name FROM subjects GROUP BY subject_name HAVING COUNT(*) > 1会找出所有在Subjects表中出现次数超过1次的科目名称（示例中就是EA）
• 外层查询先排除掉在Courses中有对应条目的记录，再筛选出属于重复科目的记录，最终就会得到你要的ID为102的EA记录

方案二：使用JOIN关联（更适合大数据量场景） #

如果你的表数据量比较大，使用JOIN的方式可能会有更好的性能：

SELECT s.*
FROM subjects s
-- 先关联到重复的科目列表
JOIN (
    SELECT subject_name
    FROM subjects
    GROUP BY subject_name
    HAVING COUNT(*) > 1
) AS repeated_subjects ON s.subject_name = repeated_subjects.subject_name
-- 左连接Courses表，找出没有匹配的记录
LEFT JOIN courses c ON s.id = c.subject_id
WHERE c.subject_id IS NULL;

语句解释：

• 首先通过子查询生成重复科目的列表，然后和原Subjects表关联，得到所有重复科目的记录
• 再通过左连接Courses表，筛选出Courses中没有对应条目的记录（c.subject_id IS NULL就表示没有匹配到）

验证结果 #

用你提供的示例数据测试这两个语句，都会返回如下结果：

ID SUBJECT_NAME COLLEGE NAME SYEAR
102 EA AAA 2016

完全符合你的需求！

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