在教务系统或者选课数据里经常会有这种需求“查一下和某位同学选的课完全一样的同学”。听起来就是一条SQL的事但真写起来你可能会发现有的写法在测试数据上看着没问题换一批真实数据就漏人有的写法能跑通但数据量一大就慢到没法忍还有的写法压根没把“完全一样”定义清楚把多选课、少选课、甚至一门课都没选的人全算进去了。这篇文章就围绕这个查询场景从业务拆解、SQL实现、性能优化到边界情况完整过一遍。适合数据分析岗位、后端开发以及刚接触SQL复杂查询、想搞懂集合语义的朋友参考。1. 先别急着写SQL把“完全一样”翻译成集合问题1.1 集合相等的本质互相包含才算相等“和xxx同学所选课程完全一样”这句话翻译成集合语言是A同学选的所有课程等于xxx同学选的所有课程。在关系型数据库里“等于”通常要落成两步验证第一步xxx选的每一门课A同学也都选了第二步A同学选的每一门课xxx也都选了。只有两个方向都成立才能说两个课程集合完全相同。很多第一次写这个需求的人下意识只会做第一步写出来的SQL类似于“找出选了xxx所有课程的同学”。这种查询能捞出一大批人但这些人里混着“多选课”的情况——因为只要覆盖了xxx的全部课程就算比你多选三五门也会被带出来。反之如果只写第二步又会混入“少选课”的人。所以集合相等的核心判定就是双向包含这也是整篇所有解法的底层逻辑。1.2 业务口径先定清楚空选课、重复记录、课程维度写代码之前有几个业务口径最好当面和需求方确认否则SQL写完很容易被挑战。第一个是空集问题。如果xxx一门课都没选按照严格的集合相等定义所有一门课都没选的同学都和他“完全一样”。但业务上往往不想把这些人算进去因为“没选课”和“选了相同课程”是两种性质。需要约定空集是否参与比较。第二个是重复记录问题。如果底层选课表因为数据质量问题存在重复行比如同一个学生同一门课出现了两次那么按COUNT统计课程总数时会出错用GROUP_CONCAT序列化时也会拼出重复的课程ID。最稳妥的做法是先对底层数据做一次DISTINCT或者建唯一约束从源头杜绝。第三个是课程维度问题。有些选课表里课程ID是唯一的但有些业务里同一门课分了不同教学班、不同上课时间甚至同一门课在不同学期开课。需求里说“所选课程完全一样”是指课程ID一样还是课程名称一样还是连教学班都一样这个不确认清楚后面所有SQL都是白写。2. 解法一双向NOT EXISTS最稳也最容易写错2.1 从“小X的每门课对方都选了”开始先建一张足够简单的选课表把问题落到具体SQL上CREATE TABLE enroll ( student_id INT NOT NULL, course_id INT NOT NULL, PRIMARY KEY (student_id, course_id) );假设小X同学的student_id是1001。第一步先找“小X的每门课对方都选了”的同学这也是大多数人的第一版写法SELECT DISTINCT e2.student_id FROM enroll e1 JOIN enroll e2 ON e2.student_id e1.student_id WHERE e1.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll x WHERE x.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll y WHERE y.student_id e2.student_id AND y.course_id x.course_id ) );这段SQL的意思可以顺着读外层选出一个候选学生e2然后内层遍历小X的所有课程x如果存在任何一门课程x在e2的选课记录里找不到对应的course_id那么这个e2就被淘汰。这里有一个非常容易看懵的点为什么用双重NOT EXISTS而不是直接NOT EXISTS因为“对于小X的每一门课对方都选了”本质上是一个全称判断SQL里没有直接的“FOR ALL”语法只能通过“不存在任何一门小X的课程是对方没选的”来表达。这也是集合语义和自然语言之间最常见的转换障碍。2.2 为什么只写半边会翻车经典漏选场景只做上面那一步把“小X课程被完全覆盖”的同学捞出来结果里会混进大量多选课的人。举个例子小X选了语文、数学A同学选了语文、数学、英语那么A完全覆盖了小X的课程第一步会把他查出来。但按照需求A的课程集合是{语文,数学,英语}小X是{语文,数学}两者并不相等。所以还必须补上反方向的验证对于候选同学的每一门课小X也要都选过。双方向完整写法是SELECT DISTINCT e2.student_id FROM enroll e1 JOIN enroll e2 ON e2.student_id e1.student_id WHERE e1.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll x WHERE x.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll y WHERE y.student_id e2.student_id AND y.course_id x.course_id ) ) AND NOT EXISTS ( SELECT 1 FROM enroll x WHERE x.student_id e2.student_id AND NOT EXISTS ( SELECT 1 FROM enroll y WHERE y.student_id 1001 AND y.course_id x.course_id ) );第二个NOT EXISTS就是翻过来的判断遍历候选学生的每一门课x如果这门课在小X的选课记录里找不到就淘汰。两个NOT EXISTS同时通过才能保证两个集合互相包含。这套解法的优点是与数据库方言无关MySQL、PostgreSQL、Oracle、SQL Server都能跑通语义直观改造成“查课程组合完全相同的所有人”也很容易。缺点是相关子查询嵌套比较深在enroll表数据量大时执行计划容易走成多次扫描后文会专门聊怎么优化。3. 解法二GROUP_CONCAT/STRING_AGG 序列化比较代码最短但坑很多3.1 不同数据库的写法对照如果把一个学生的所有课程ID按固定顺序拼成一个字符串再把小X的课程ID拼成另一个字符串两个字符串相等就说明课程集合一致。这个思路实现起来最直观代码也最短。但不同数据库的聚合拼接函数完全不同数据库聚合拼接函数写法要点MySQLGROUP_CONCAT需要指定ORDER BY注意group_concat_max_len限制PostgreSQLSTRING_AGG需要把course_id转成text并指定ORDER BYSQL Server 2017STRING_AGG配合WITHIN GROUP (ORDER BY ...) 使用OracleLISTAGG配合WITHIN GROUP (ORDER BY ...) 使用SQLiteGROUP_CONCAT本身不保证顺序需要先排序子查询以MySQL为例查询语句可以这样写SELECT student_id FROM enroll WHERE student_id 1001 GROUP BY student_id HAVING GROUP_CONCAT(course_id ORDER BY course_id SEPARATOR ,) ( SELECT GROUP_CONCAT(course_id ORDER BY course_id SEPARATOR ,) FROM enroll WHERE student_id 1001 );PostgreSQL写法类似只是函数名和类型转换有区别SELECT student_id FROM enroll WHERE student_id 1001 GROUP BY student_id HAVING STRING_AGG(course_id::text, , ORDER BY course_id) ( SELECT STRING_AGG(course_id::text, , ORDER BY course_id) FROM enroll WHERE student_id 1001 );这个思路的优点是写法简洁、适合快速验证结果也适合临时跑数。缺点也非常明显细节坑很多下面逐个说。3.2 排序、分隔符、类型、长度四个坑第一个坑是排序。课程ID拼接必须保证顺序一致否则{1, 10}和{10, 1}拼出来的字符串不一样但集合本身是相同的。ORDER BY如果不写GROUP_CONCAT的默认顺序是不确定的哪怕同一套数据跑两次也可能得到不同结果必须显式ORDER BY course_id。第二个坑是分隔符。拼接时选的符号必须保证“不会出现在任何课程ID里”。如果course_id是数字逗号安全如果course_id是字符串且本身可能包含逗号就需要换成其他分隔符比如竖线或者下划线。更稳妥的做法是在拼字符串之前给每个课程ID再加一层定界符比如把课程ID拼成#1#,#2#,#3#避免边界歧义。第三个坑是类型问题。数字类型直接拼接没问题但字符串类型拼接出的结果可能会有歧义。比如课程ID的金字塔式问题课程ID为1、10的两个课程排序后拼成“1,10”课程ID为1、1、0的课程如果有的话排序后拼成“1,1,0”。这两个字符串完全不同但集合语义在某些业务下可能一样。实际中一般不会出现这种情况但如果你要处理的课程ID是带前导零的字符型比如001和1就会存在拼串判断失效的风险这时更适合用双向NOT EXISTS那套方案。第四个坑是长度限制。MySQL的GROUP_CONCAT默认最大长度是1024字节如果学生选的课程特别多或者课程ID字符串很长拼接结果会被截断导致HAVING判断出错。可以在会话级别先调大SET SESSION group_concat_max_len 102400;这套方案还有一个隐藏问题数据库的字符串比较是区分大小写的如果课程ID本身包含字母排序和拼接时的大小写规则需要统一。否则只是大小写不同但代表同一门课的记录会得出“不相等”的错误结论。综合来看序列化方案适合课程不多、课程ID简单、数据量小的场景生产环境还是要谨慎。4. 解法三集合差集与COUNT预筛大表上更实用的组合拳4.1 EXCEPT/INTERSECT 视角下的等价定义如果数据库支持集合运算可以换一个思路两个集合相等等价于“A的课程集合减去B的课程集合为空”并且“B的课程集合减去A的课程集合为空”。用SQL表达就是两个NOT EXISTS也可以用EXCEPT直接写。以PostgreSQL为例SELECT student_id FROM enroll WHERE student_id 1001 AND NOT EXISTS ( SELECT course_id FROM enroll WHERE student_id 1001 EXCEPT SELECT course_id FROM enroll WHERE student_id enroll.student_id ) AND NOT EXISTS ( SELECT course_id FROM enroll WHERE student_id enroll.student_id EXCEPT SELECT course_id FROM enroll WHERE student_id 1001 );这个写法在语义上是最接近“集合相等”定义的一眼就能看出两个方向都要为空。MySQL没有EXCEPT只能继续用NOT EXISTS表达。Oracle里对应的是MINUSSQL Server对应的是EXCEPT。用集合差集最大的好处是逻辑清晰容易排查问题。比如发现结果多了可以单独执行第一个NOT EXISTS看哪些人“覆盖了小X的课程但没有被小X覆盖”发现结果少了就单独执行第二个NOT EXISTS看哪些人被误杀了。这种“把问题拆成两半单独验证”的做法比盯着整条SQL猜要高效得多。4.2 先比课程总数再双向验证性能能翻多少倍上了真实数据量以后双向NOT EXISTS嵌套会导致执行计划非常恐怖。假设表里有5万学生、20万条选课记录候选学生数量是5万级别每个学生都要做一次“遍历小X的所有课程”和“遍历自己的所有课程”的嵌套查询即使有索引相关子查询的执行次数也会非常多。一个非常实用的优化是先比较课程总数。如果两个学生选的课数量不一样那课程集合必然不相等这个条件能提前淘汰掉绝大多数候选人。课程总数可以一次性算出来放子查询里参与JOIN把“对所有候选学生做双向验证”缩小成“只对课程数与总课程数一致的学生做双向验证”。优化后的完整写法如下SELECT t2.student_id FROM ( SELECT student_id, COUNT(*) AS cnt FROM enroll GROUP BY student_id ) t2 JOIN ( SELECT COUNT(*) AS cnt FROM enroll WHERE student_id 1001 ) t1 ON t1.cnt t2.cnt WHERE t2.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll x WHERE x.student_id 1001 AND NOT EXISTS ( SELECT 1 FROM enroll y WHERE y.student_id t2.student_id AND y.course_id x.course_id ) ) AND NOT EXISTS ( SELECT 1 FROM enroll x WHERE x.student_id t2.student_id AND NOT EXISTS ( SELECT 1 FROM enroll y WHERE y.student_id 1001 AND y.course_id x.course_id ) );课程数不等的学生根本进不了这条查询的候选集。假设平均每个学生选10门课5万学生里课程数正好也是10门的人数可能只有几千人再在这几千人里做双向NOT EXISTS性能差距可以到一个数量级以上。实际测试中我见过原本跑几十秒的查询优化到几百毫秒。4.3 索引与执行计划的实际调优点这个查询主要涉及两类条件一是按student_id找一个人的所有选课记录二是按course_id判断某门课是否在某人的记录里。所以enroll表上要建两个方向的索引CREATE INDEX idx_student ON enroll(student_id, course_id); CREATE INDEX idx_course ON enroll(course_id, student_id);第一组索引服务“按学生查课程”的情况第二组索引服务“按课程查学生”的情况。EXPLAIN的时候重点关注两个地方一是嵌套循环里的索引查找是否走了索引而非全表扫描二是候选集是否经过了COUNT预筛之后才进入相关子查询。还要提醒一点GROUP BY student_id这条预筛SQL在数据量大的时候本身也可能慢。如果enroll表有大量重复记录先做DISTINCT再COUNT更稳。如果课程数量普遍很大COUNT(*)的结果分布会很稀疏预筛效果会变差这时候可以考虑在应用层用“课程数量直方图”先圈定可能相等的课程数范围再下推到SQL。5. 扩展场景把课程组合完全相同的所有人一次性揪出来5.1 按课程组合分组而不是只对比小X前文所有写法都围绕“给定小X学号找和他相同的人”。实操中经常有反向需求不指定具体哪位同学直接把所有“课程组合完全相同”的人归成一组看看多少人选了同一套课。这时候可以继续用GROUP_CONCAT/STRING_AGG做分组键SELECT student_id, GROUP_CONCAT(course_id ORDER BY course_id SEPARATOR ,) AS course_group FROM enroll GROUP BY student_id;然后按course_group分组聚合并筛出人数大于1的课程组合SELECT course_group, COUNT(*) AS student_cnt FROM ( SELECT student_id, GROUP_CONCAT(course_id ORDER BY course_id SEPARATOR ,) AS course_group FROM enroll GROUP BY student_id ) t GROUP BY course_group HAVING COUNT(*) 1 ORDER BY student_cnt DESC;这个扩展看起来只是把“和小X比较”换成了“所有人两两比较”。它能帮你发现很多业务上意想不到的现象比如某门热门公共课是几乎所有人的“标配”导致几千人课程组合完全相同又比如某些学生选课高度雷同背后可能是同一个导师指导选课也可能说明选课系统存在批量刷课的异常行为这类分析在教务管理和数据质检里都非常有用。5.2 自测验证用“反例”证明SQL没写错SQL写完之后很多人只验证了“查出来的这些同学课程是否和小X一致”却忽略了一个更重要的反方向验证没有查出来的人是不是真的都不一致。这是个容易忽略的盲区。我自己的习惯是先用小样本数据手动验证。比如造一张只有5个学生、10门课程的小表手工构造这5个人的选课集合其中两两相同的人必须能被SQL查出课程集合差一门的人绝对不能出现在结果里。然后直接用EXCEPT写法和小X的课程集合做差集人工核对差集结果是否为空。另一个更简单的逻辑自测是对每条结果分别统计“结果同学的课程数”和“小X的课程数”应该完全相等再统计“结果同学有而小X没有的课程数”和“小X有而结果同学没有的课程数”都应该为0。如果任何一个断言不成立那SQL一定有问题。把这个验证脚本固化下来以后改SQL结构或者优化性能时回归测试也方便。6. 常见问题与避坑清单速查现象可能原因解决方案查出了多选课的同学只做了“小X课程被对方覆盖”的方向缺少反向验证补上第二个NOT EXISTS或EXCEPT方向没选课的同学被全部带出空集比较口径没约定业务确认后在SQL里加CNT0过滤GROUP_CONCAT结果不稳定没写ORDER BY拼接顺序随机显式GROUP_CONCAT(course_id ORDER BY course_id)拼出的字符串被截断MySQL group_concat_max_len太小调大group_concat_max_len或用集合解法替代查询极慢候选学生太多缺少课程总数预筛所有学生都进了嵌套验证先按COUNT(*) JOIN预筛再做双向NOT EXISTS结果少了真实同学底表存在重复选课记录COUNT口径不对先DISTINCT或对(student_id, course_id)建唯一约束字符串比较误判不同课程ID是字符串类型存在前导零/大小写问题改用集合差集或双向NOT EXISTS方案课程ID是数字但排序后拼接出错未注意类型转换排序按字典序而非数值序ORDER BY CAST(course_id AS UNSIGNED) 后再拼接再补充一个容易踩的暗坑如果选课表里同一个学生同一门课出现两次比如补选、退课记录没清理干净那么COUNT(*)算出的课程数会比实际课程数多COUNT预筛阶段就可能把正确同学过滤掉。这种情况建议在第一层就做SELECT DISTINCT保证每个学生每门课只保留一条记录。数据质量本身不好的表任何SQL优化都建筑在沙子上先清洗数据再谈查询效率。结尾我个人的习惯是面对“和某人选课完全一样”这类需求第一步永远是找业务方把“完全一样”的口径钉死第二步才是打开SQL编辑器。定义清楚“是否包含空集”“课程ID是否唯一”“同一课程不同学期怎么算”这三个问题比任何写法和优化都重要。SQL本身无非是集合思想的翻译翻译错了跑得再快也是错的。最后分享一个小技巧每次写完这种集合类SQL我都会顺手造两条正反样例数据来测试——一条课程集合完全一致一条只差一门课。如果两条样例在结果里一有一无代码才算真正过关。这个习惯帮我避免过很多次“测试数据刚好没踩到坑、上线就翻车”的尴尬。