刷SQL题刷到这道题时很多人第一反应是“这不就是按managerId分组然后数人头吗”但真上手写就会踩进几个坑里。题目编号570在LeetCode的数据库题库里属于中等难度但它在面试里出现的频率相当高。我见过不少候选人能写出正确结果却答不上来“为什么这里要用子查询”“能不能用自连接”“COUNT里到底要不要去重”这类追问。这篇就从头到尾把这道题拆开讲透包括我实际跑数时习惯用的几种写法以及它们在不同数据库引擎下的表现差异。1. 题目拆解别把“至少5名直接下属”想简单了1.1 原始表长什么样这道题给了一张Employee表关键字段就三个id员工唯一标识、name姓名、managerId该员工的直属经理ID。题目要求我们找出所有直接下属数量不少于5人的经理输出他们的姓名。这里最容易被忽略的一句话是经理也是员工所以经理本人在同一张表里也有自己的记录他的managerId指向更上一级。很多人一拿到表就开始按managerId分组却忘了先确认哪些人是“经理”哪些人只是普通员工。我把表结构列出来方便对照id员工编号主键每个员工唯一name员工姓名managerId该员工的直属上级编号如果是顶层领导则为NULL目标输出满足条件的经理姓名注意题目只要输出name不需要输出该经理的id也不需要输出他手下的人数。但我们在解题过程中为了验证结果正确通常会先把人数算出来看一眼。1.2 隐含条件的三个关键点第一经理必须存在于Employee表中。也就是说如果一个managerId指向的编号在表里找不到对应记录这个人不能算作经理。现实业务中可能存在“上级已经离职但下级记录没更新”的情况这题默认数据是自洽的但写成SQL时我们仍然要养成“存在性校验”的思维习惯。第二所谓直接下属是指managerId字段直接等于经理id的记录。隔级汇报不算跨部门虚线汇报不算这题只认这一层直属关系。这一点和很多企业组织架构里的“实线经理”概念一致。第三至少5人意味着COUNT结果要用 5来判断而不是 5。有些帖子在写题解时把这个边界条件写错了导致5个下属的经理被漏掉。面试时如果你能主动提一句“这里要5”会显得你考虑问题很周全。1.3 我先说一个容易错的直觉大多数人的第一版SQL是这么写的SELECT name FROM Employee GROUP BY managerId HAVING COUNT(*) 5;表面看没什么问题分组、计数、过滤一气呵成。但仔细想一下GROUP BY managerId之后你拿到的每一组是“某个经理的下属集合”而不是“经理本人”。且不说name根本无法直接SELECT出来就算有些数据库开了ONLY_FULL_GROUP_BY宽松模式允许你取任意行的name取出来的也未必是经理的姓名。这就是这道题第一个真正要跨越的门槛我们要的“经理姓名”是分组键那一行自己的信息而不是下属组里的信息。分组天然地会把视角放在“下属侧”但题目要的答案在“经理侧”。2. 从数据视角理解一次查询里同时扮演“下属”和“经理”2.1 用生活化类比建立直观你可以把Employee表想象成一家公司的花名册每一行是一个员工。现在要求“找出带了至少5个兵的排长”。直观做法是先把所有“兵”按照他们的“排长编号”归类数出每个排长名下有多少兵再去看这些排长是谁。把这句话翻译成SQL就是先按managerId分组数人数再拿着这些排长编号回到原表里查他们的名字。数据量小的时候你完全可以分两步走先查出一个带人数的编号列表再用WHERE id IN (...)去原表取名字。这个两步走的过程写成一条SQL就是子查询或者自连接。2.2 为什么子查询是“标准答案”LeetCode上最常见的解法是SELECT e.name FROM Employee e WHERE e.id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) 5 );我来逐行解释一下这个写法的执行逻辑。内层查询先对所有员工按managerId分组统计每个经理手下的直接下属人数用HAVING过滤出人数不少于5的经理编号集合。外层查询再从原表里按id IN取出这些经理的姓名。这个写法最符合人的直觉也最容易向面试官讲明白。实际上内层查询为了保险通常还会加一个WHERE managerId IS NOT NULL防止GROUP BY把NULL单独分成一组——虽然这题里NULL那一组肯定不满足HAVING COUNT(*) 5但如果哪天你手滑把阈值改成 0NULL组就会混进来。2.3IN与JOIN在这个场景里的区别很多教程说子查询慢要用JOIN代替IN。这个说法在旧版本MySQL里有一定道理因为优化器对IN子查询的处理不够聪明可能会产生逐行执行子查询的糟糕计划。但现代数据库MySQL 8.0、PostgreSQL、SQL Server等对这类半连接semi join已经优化得很好IN和JOIN在语义上有细微差别但性能差距通常可以忽略。不过从语义严谨性来说IN在这里其实比JOIN更精确。因为IN不会因为经理有多条匹配记录而让结果重复而JOIN如果管理不好很容易把同一个经理的行放大成多行最后还得靠DISTINCT去补救。3. 三种主流解法逐项对比子查询、自连接、窗口函数3.1 解法一分组子查询最稳妥的入门写法上面已经展示过具体SQL这里补充一个我在生产环境里会加的小改动给内层查询的COUNT显式指定计数字段。SELECT e.name FROM Employee e WHERE e.id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(e2.id) 5 );等等这里有个陷阱内层查询里并不能直接引用e2.id。让我把完整写法写清楚SELECT e.name FROM Employee e WHERE e.id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) 5 );在GROUP BY managerId的分组里COUNT(*)统计的是每组行数也就是该经理的直接下属人数。因为每一条员工记录只属于一个经理所以不存在重复计数的问题。这个场景下用COUNT(*)和COUNT(1)没有区别但不要写成COUNT(managerId)——COUNT(managerId)会跳过NULL值而managerId在分组里必然是非NULL的NULL自成一组且那一组根本进不了HAVING所以结果恰好一样。但语义上COUNT(managerId)让人困惑建议统一用COUNT(*)。3.2 解法二自连接理解JOIN语义的代表作自连接是这道题第二个经典解法SELECT DISTINCT m.name FROM Employee e JOIN Employee m ON e.managerId m.id GROUP BY m.id, m.name HAVING COUNT(e.id) 5;逻辑是这样的把表同时当作“员工表”和“经理表”员工表的managerId去匹配经理表的id匹配成功说明这位员工是这位经理的直接下属。然后按经理的id和name分组数员工数。加DISTINCT是担心一个员工多次匹配到同一个经理正常情况下不会因为managerId是单值但为了保险起见加上也不算错。GROUP BY m.id, m.name已经很严谨这种情况下m.name依赖m.id完全符合函数依赖规则任何数据库的ONLY_FULL_GROUP_BY模式都不会报错。这道题里JOIN和子查询的差异主要体现在JOIN会把经理和下属的配对关系全部展开成行再压缩回经理维度。如果一张表有几万员工JOIN的中间结果集理论上有几万行不算大但如果表里有自关联的复杂结构JOIN可能会产生意外的行数膨胀。所以我在面试中通常建议先讲子查询版本如果面试官追问性能再补充JOIN版本。3.3 解法三窗口函数给你一个“滑动窗口”的新视角窗口函数解法不是这道题的最高效写法但它体现了一种更现代的SQL思维SELECT DISTINCT name FROM ( SELECT e.name, COUNT(*) OVER (PARTITION BY e.managerId) AS cnt FROM Employee e ) t WHERE cnt 5 AND name IS NOT NULL;内层窗口函数按managerId分区统计每个分区内的行数这个行数就是该经理的直接下属人数。注意窗口函数不会折叠行所以每个员工的记录旁都会挂一个自己所属经理的下属总数。外层再按这个总数过滤。这里有一处必须小心窗口函数的分区键是managerId而name是员工自己的名字。过滤条件里cnt 5命中的是“经理的下属组”但这一行本身可能是经理本人也可能是某个下属。比如经理A带了5个下属那么这5条下属记录加上经理A自己的记录一共6行都会带上cnt5。直接SELECT DISTINCT name会把下属的姓名也捞出来造成严重的错误。解决办法是在内层把“经理本人的行”和“下属行”区分开。通常的做法是只在经理本人的行上展示他的下属数这可以通过CASE WHEN id managerId之类的条件实际上不可能有这种行或者更实际的做法是SELECT DISTINCT m.name FROM ( SELECT managerId AS mid, COUNT(*) OVER (PARTITION BY managerId) AS cnt FROM Employee WHERE managerId IS NOT NULL ) t JOIN Employee m ON m.id t.mid WHERE t.cnt 5;这样内层查询的每一行都是“下属视角”然后把下属对应的经理编号关联回Employee表取经理姓名。窗口函数在这个题目里的意义更多是练习语法而不是最优方案。但如果你面试的公司用的大数据组件支持窗口函数而表又是超大宽表窗口函数可以避免一次自关联的IO开销这个优势在特定场景下是实打实的。3.4 三种解法的对比总结解法核心语法易错点性能表现分组子查询GROUP BY HAVING IN内外层角色混淆两个全表扫描适合中小表自连接JOIN GROUP BY结果膨胀需DISTINCT一次自关联索引合理时表现好窗口函数OVER (PARTITION BY)分区键与输出行不匹配避免分组折叠大表友好我个人的建议是如果你在面试优先用子查询讲清楚思路然后提一句“如果数据量很大我会用自连接配合索引优化”如果你在日常报表里碰到类似需求直接按你熟悉的引擎选型——MySQL里我用子查询PostgreSQL里我反而喜欢JOIN写法。4. 实操避坑分组、去重与NULL的连环坑4.1ONLY_FULL_GROUP_BY下的经典报错MySQL 5.7及以上默认开启sql_mode里的ONLY_FULL_GROUP_BY这时候你写SELECT name FROM Employee GROUP BY managerId;大概率会收到类似“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause”的报错。因为name既不参与分组也不是聚合函数和managerId之间没有函数依赖关系。早期MySQL允许这种“非严格模式”下的随机取值但取出来的name根本不可控可能是该组里任意一行的名字。很多初学者遇到这个报错后第一反应是给name套个MAX()糊弄过去比如SELECT MAX(name) FROM Employee GROUP BY managerId这虽然能跑但语义是完全错误的——取的是字典序最大的名字跟经理是谁毫无关系。正确思路永远是把“计数”和“取名字”拆开或者通过自连接让name跟着经理的id走而不是从下属组里抓一个名字顶上。4.2 NULL值的三个隐藏陷阱第一个隐藏陷阱是managerId为NULL的顶层员工如果被GROUP BY捕获会自成一组。一般情况下这一组计数为1不可能满足HAVING COUNT(*) 5所以不影响结果。但如果你在写自连接版本时用了ON e.managerId m.idNULL永远不会等于任何值顶层员工不会出现在JOIN结果里——这恰好是正确行为。第二个隐藏陷阱是自连接版本里如果一张表里有“员工id为NULL”的记录虽然主键不允许但实际脏数据里真见过JOIN条件会漏掉这些行导致计数值偏小。稳妥做法是建表时就用NOT NULL约束把空值堵死。第三个隐藏陷阱和这道题无关但影响更深远如果使用COUNT(managerId)代替COUNT(*)在分组计数时NULL会被忽略。虽然这道题里managerId为NULL的员工独立成组即使忽略NULL也不会影响其他组但如果你把这个习惯带到别的题目里比如数“每个部门有多少人”时用了COUNT(manager_id)而manager_id恰好允许NULL人数就会悄悄变少。所以我一直强调计数用COUNT(*)明确要对某个字段做非空计数时才用COUNT(column)。4.3 数据量增大时索引该建在哪这道题的测试数据都很小LeetCode上不建索引也能秒出结果。但放到真实业务里一张几百万行的员工表查询就会明显变慢。从执行计划的角度来看子查询的GROUP BY managerId需要扫描全表并按managerId排序或哈希外层WHERE id IN (...)需要按主键索引快速定位。所以推荐索引是CREATE INDEX idx_employee_manager ON Employee(managerId, id)。managerId用于分组id作为覆盖列可以避免回表。如果查询还要过滤managerId IS NOT NULL这个索引也能派上用场。自连接版本同理连接键是e.managerId m.idm.id是主键自带索引所以只需要给e.managerId建索引即可。不过索引不是万能的。如果表的统计信息过期优化器可能放弃索引选择全表扫描。我在排查慢查询时第一步永远是EXPLAIN看执行计划而不是盲目加索引。5. 从LeetCode到真实业务这道题的“表亲”们5.1 真实场景中的“经理带人”报表LeetCode的题目虽然做了简化但“按某列分组后回查主表信息”这种模式在报表开发里随处可见。我举个例子假设你是运营团队的数据分析师想找出“创建了至少10个有效工单的一线客服”表结构可能是Ticket(工单ID, 客服ID, 状态)你要先按客服ID分组数有效工单再回Staff表取客服姓名和所属小组。这几乎就是570题的翻版。再比如电商领域要找出“名下至少有50个在售商品的店铺”同样是Product(商品ID, 店铺ID, 上架状态)先分组计数再回Shop表取店铺信息。这种“子查询取ID集合外层回查详细信息”的模式在各类管理报表、风控名单、运营活动筛选中出现频率极高。可以说把570题的思路吃透你解决的不只是一道题而是一整类“Haves”问题——找出满足某种数量门槛的实体。5.2 组织架构表比这题复杂十倍的版本现实中的组织架构往往不是单层“员工-经理”而是多层树形结构。比如一张org_tree表里同时有emp_id、manager_id、department_id、level字段要找出“整个部门层级下累计超过20人的二级经理”就需要递归CTE或者树形遍历。这时候GROUP BY managerId就不再够用你得先算出每个节点的子树人数。以PostgreSQL为例可以用递归CTEWITH RECURSIVE tree AS ( SELECT id, manager_id, 1 AS depth FROM employee WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, t.depth 1 FROM employee e JOIN tree t ON e.manager_id t.id ) SELECT ... -- 后续按子树聚合但这类查询的难点在于“子树聚合”的写法实际实现时往往需要反向传播或使用闭包表。如果你们公司的组织架构变更是常态人员调动频繁闭包表closure table会比递归CTE更高效。这部分内容展开就是另一篇长文了这里提一嘴主要是帮你建立“570题是最简模型”的认知避免在真实业务里生搬硬套单表写法。5.3 在Hive、Spark SQL等大数据引擎中的写法差异如果你的公司用的是Hive或Spark SQL同样的逻辑在语法上几乎不用改但有几个细节要注意。第一Hive对IN子查询的支持有版本限制Hive 2.2.0之前不支持IN后面接子查询建议改用LEFT SEMI JOIN或JOIN。Spark SQL从2.4开始对IN的优化比较完善可以直接用。第二大数据引擎里数据量动辄上亿行GROUP BY会产生shuffle这时候要关注数据倾斜。比如某个大区经理手下有几万人而其他经理只有几个人按managerId分组时那个大组会成为一个热点。如果这道题放到超大规模数据上可以考虑加一层“按部门分区”的预处理或者使用COUNT(DISTINCT)配合MAPJOIN做优化——不过这些都是后话了LeetCode场景完全用不上。第三窗口函数解法在Spark SQL里性能不错因为Spark的窗口计算对PARTITION BY做了优化。如果你在用PySpark处理这类数据我反而推荐先用DataFrame API做groupBy(managerId).count().filter(count 5)然后再join原表取名字。这个思路和SQL子查询版本完全对应。6. 面试追问与延伸思考把70分的答案讲到90分6.1 面试官最爱追问的四个问题第一个追问“如果经理的下属调走了表里会立刻删除记录吗”这题考的是业务理解。实际系统里可能会有status字段在职/离职不能只从表里是否有记录来判断。如果题目加上status active条件你的SQL里就要多一层过滤。回答时可以主动说我理解这道题隐含的前提是所有记录都有效如果有软删除字段我会在子查询里加上状态过滤。第二个追问“为什么用HAVING而不是WHERE”这是基础中的基础。WHERE在分组前过滤行HAVING在分组后过滤组。计数条件必须在对组聚合之后才能判断所以只能用HAVING。如果面试官再加问“能不能把COUNT(*) 5写进WHERE”答案是不能除非你用窗口函数先在子查询里算出计数再在外层WHERE过滤——这其实正是第3节里窗口函数解法的思路。第三个追问“COUNT(*)和COUNT(DISTINCT 字段)有什么区别”如果经理和下属之间存在唯一约束每个员工只有一个经理COUNT(*)就够了。但如果表结构允许一个员工有多条记录比如兼职、历史记录就必须用COUNT(DISTINCT e.id)。这个追问考察的是你写SQL时是否主动考虑数据质量。答得好可以直接加分。第四个追问“如果表特别大磁盘IO是瓶颈你会怎么优化”这就进入性能调优领域了。能答出“先过滤无效数据建立复合索引避免不必要的回表把子查询改成JOIN让优化器有更多选择”基本合格。能进一步答出“使用物化视图预聚合或者把这道题的查询做成增量更新的汇总表”就是加分项。6.2 从这道题顺藤摸瓜的进阶练习题如果给你一道“找出在所有部门都工作的员工”LeetCode 1045你会立刻发现它需要GROUP BY customer_id和COUNT(DISTINCT product_key)再和部门总表做对比。这就是570题“把计数结果和条件比对”的延伸。再比如“每个部门工资最高的员工”LeetCode 184核心是GROUP BY departmentId加MAX(salary)然后回查员工信息。这又是“分组聚合后回查原表”的同类模式。可以说570题练的是SQL综合能力里最基础也最重要的一环聚合查询与表关联的组合使用。刷透这一题后面遇到同类问题你会觉得轻松很多。7. 我在实际跑数时的一点体会这道题我前前后后在面试、教学和实际报表开发里写过很多遍每次写都有新体会。最简单的一个体会是拿到SQL题先别急着敲键盘在纸上把“分组键”“计数键”“回查键”标出来——你按什么维度分组数的是哪一类的数量最终要取的是哪张表的哪个字段。把这三点写清楚SQL基本不会跑偏。还有一个容易忽略的点LeetCode的判题环境对数据库版本有说明比如MySQL 8.0的默认行为可能和5.7不同。如果你在本地5.7跑通的SQL直接提交到8.0环境偶尔会遇到窗口函数、正则表达式等语法差异。所以刷题时最好先看清楚题目页面上标注的数据库版本避免在自己环境中反复试错。最后分享一个查优化器执行计划的习惯。无论你选哪种解法提交前我都会先跑一次EXPLAIN确认没有出现“Using temporary; Using filesort”这种隐藏性能隐患。这道题数据量小看不出差别但好习惯能让你在真正处理几亿行数据时少挨几次痛。