1. 题目初识与场景映射1.1 从LeetCode 570看真实业务需求LeetCode 570这道题题目很简单给定一张Employee表找出所有至少有5名直接下属的经理。我第一次刷到这道题时第一反应是“这不就是GROUP BY COUNT一下吗”但真正上手之后发现里面牵扯到的细节比你想象的多。尤其是围绕NULL值的处理、自连接的写法、以及后续扩展到多层组织架构时的思路都很值得拿出来单独聊聊。这道题在LeetCode上属于中等难度但它在面试里出现的频率非常高。原因很简单它考察的不仅仅是“会不会写SQL”而是你能否把一个业务描述翻译成正确的表关系和聚合逻辑。在真实的企业数据中“找出管理幅度超过5人的管理者”“识别团队规模过小的负责人”“统计每个部门的主管有效管理人数”这类需求几乎每周都会出现说白了这就是组织人事分析中最常见的“管理幅度”查询。我们先把表结构还原出来。通常这道题给的是这样一张表Employee( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), managerId INT )id是员工唯一标识name是姓名department是部门managerId是该员工的直接上级的id。注意这里没有salary没有hire_date就是为了把注意力集中在“上下级关系”这一个核心上。managerId为NULL的行说明该员工没有上级通常就是CEO或者独立负责人。最终要输出的列是经理的name或者经理的id和name。LeetCode返回的case里只需要经理的姓名但如果放在实际场景中通常还要带上id、部门信息或者下属人数方便后续做处理。1.2 这个需求背后的“管理幅度”概念有至少5名直接下属的经理本质上是在测量每位管理者的“管理幅度”span of control。这个概念在组织管理里很常用团队过小说明管理者冗余团队过大说明管理压力高。一般互联网企业里技术经理的管理幅度在6到10人左右比较健康但不同行业波动很大。所以这道题虽然是个LeetCode题但它映射的是真实世界的组织诊断需求。如果你想在简历上写一个“组织健康度分析”的项目这道题的思路就是最底层的计算逻辑统计每个管理者的直接下属人数然后按阈值过滤。理解了这一点你再看下面几种解法就不会只盯着“能不能跑通”看而是会去想每种解法在面对真实脏数据时哪个更稳、哪个更快、哪个更易读。2. 解法一自连接 GROUP BY最贴近直觉的思路2.1 为什么自连接是首选先说最直接的办法。我们要统计每个经理的下属人数就得把“经理”和“下属”关联起来。在只有一张表的前提下靠什么关联靠的是Employee.id和Employee.managerId这两个字段的对应关系。这就是典型的自连接场景把同一张表当作两张表来用一张当经理表一张当下属表。第一次写的人很容易走弯路想着用GROUP BY把managerId分组然后直接COUNT最后JOIN回原表拿名字。这个思路没问题但如果直接对managerId分组而不先跟id对齐拿到的是一个managerId列表和对应的人数之后还是要回表查经理信息多了一步。自连接的好处在于关联的时候就已经把经理的行数据和下属的聚合结果绑定到一起了。2.2 标准写法与执行细节先看完整的SQLSELECT m.name FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) 5;这段SQL的逻辑拆开看分四步第一步把Employee表复制成两份分别起别名mmanager和eemployee。在物理执行层面这并不代表数据库真的复制了一份数据只是逻辑上的两个引用。第二步执行JOIN。JOIN条件是m.id e.managerId意思是“把每一个经理和所有把经理ID填在自己managerId字段里的员工匹配到一起”。这一步做完之后返回的中间结果集里每一行都是一对“经理-下属”的组合有多少下属经理的行就会出现多少次。第三步GROUP BY m.id, m.name。因为每个经理现在对应多行下属数据我们按经理分组然后对每组内e.id计数就得到该经理的直接下属人数。第四步HAVING COUNT(e.id) 5。HAVING和WHERE的区别我刚学SQL时总是记混。WHERE是在分组之前对原始行做过滤而HAVING是在分组聚合之后对分组结果做过滤。这里我们没法用WHERE因为COUNT是聚合之后才产生的值。这里有一个执行细节需要注意GROUP BY m.id, m.name里的m.name要不要加取决于你SELECT了什么。如果你SELECT的是m.id和m.name那GROUP BY里就必须同时出现这两个字段否则在SQL标准里是不合法的MySQL的ONLY_FULL_GROUP_BY模式也会直接报错。当然你也可以只GROUP BY m.id然后SELECT里用MIN(m.name)或者ANY_VALUE(m.name)来取姓名因为id是主键每个id对应的name是唯一的这样写也没问题。但最保险、最好读的写法还是把两个字段都放进GROUP BY。2.3 为什么COUNT(e.id)而不是COUNT(*)写这个查询时很多人会写成COUNT()结果也没错。但严格来说COUNT(e.id)更严谨。原因在于JOIN过程中如果e.id是NULLCOUNT()也会算进去而COUNT(e.id)不会。虽说当前表结构里id是主键不可能为NULL但如果你后续把表结构调整成联合主键或者从外部系统导数据时混入了空值COUNT(e.id)能帮你挡住这种脏数据。另外HAVING条件里到底写COUNT(e.id) 5还是COUNT(*) 5效果在当前数据下完全一致。我习惯写COUNT(e.id)因为在语义上我们关心的是“有多少个有合法ID的下属”而不是“有多少行关联记录”。2.4 这个解法的优缺点优点非常明显逻辑直白SQL可读性高任何拿过初级SQL培训的人都能看懂。而且它在大多数数据库上执行效率都不错尤其是加了索引之后。缺点在于如果Employee表非常大自连接会产生一个很大的中间结果集。假设有1万个经理、每个经理平均10个下属JOIN出来的中间结果就是10万行如果经理多、下属更多中间结果就会膨胀得比较厉害。当然现代数据库优化器一般不会真的把整个连接结果都物化出来但如果你用那种比较老的数据库或者配置较低的OLTP实例还是能感觉到性能差距。3. 解法二子查询先把人数算出来再回表取名字3.1 换个思路先聚合、后关联自连接虽然直观但并不是唯一解甚至在某些场景下不是最优解。第二种常见的做法是先用子查询按managerId分组统计出人数超过阈值的管理者ID再用这些ID去Employee表里取名字。这种思路更“模块化”第一步解决“哪些经理达标”第二步解决“这些经理是谁”。3.2 使用IN子查询的写法SELECT name FROM Employee WHERE id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) 5 );这个写法看起来比自连接简洁不少而且逻辑非常清爽内层查询先统计每个managerId对应的下属人数过滤出至少5人的那些managerId外层查询再把id匹配进去取出姓名。注意一个潜在问题如果managerId列里有NULL那在GROUP BY managerId时NULL会单独成组COUNT(*)也能统计出结果。但在一般情况下managerId为NULL的员工根本没有上级他们也不会被任何查询关联上所以在做内层查询时严格来说应该先排除掉NULLSELECT managerId FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(*) 5;有的数据库在GROUP BY时会把NULL单独作为一组如果你写死了HAVING COUNT(*) 5NULL组因为下属人数起码是1就是那个人本身一旦满足条件反而会被选中而实际上它根本不是一个有效经理。更干净的做法是直接用上面这个排除NULL的版本。3.3 EXISTS改写语义更清晰除了IN还可以用EXISTS。两种写法在结果上等价但语义上略有差别。IN是先执行子查询、把结果集缓存起来再去外层匹配EXISTS则是“外层每取一行就去子查询里检查是否存在满足条件的记录”。SELECT name FROM Employee AS m WHERE EXISTS ( SELECT 1 FROM Employee AS e WHERE e.managerId m.id GROUP BY e.managerId HAVING COUNT(*) 5 );这个写法稍微绕一点但它更接近自然语言“如果存在这样一个下属集合该集合的经理是当前这个员工且集合内人数至少5人那么当前员工就是我们要找的经理。”它和IN版本的核心逻辑一致关键还是GROUP BY HAVING这套组合拳。3.4 什么时候用IN什么时候用EXISTS早年有一种说法是“IN比EXISTS慢因为子查询要全量执行”但在现代数据库优化器里这个差异已经基本被抹平了。MySQL 5.6之后对IN和EXISTS都做了优化改写PostgreSQL的规划器也很聪明会基于统计信息选择合适的连接策略。所以在写业务代码时我优先考虑的是可读性而不是这点性能差异。但在一个场景下EXISTS会比IN更有优势当子查询结果集非常大而外层表过滤性又很好的时候EXISTS因为可以提前终止找到一条就返回往往能减少不必要的计算。反过来如果子查询结果集很小IN会先把小集合物化出来后续匹配很快这时IN更合适。这道题里内层子查询结果最多也就是“有下属的经理ID”通常不会太庞大两种写法性能差距可以忽略。选哪种看你团队里其他人更容易读懂哪种。4. 解法三窗口函数COUNT OVER一网打尽4.1 窗口函数让“每个经理的下属数”随行展示如果说前面两种解法是“分组聚合后取结果”那窗口函数解法就是“在保持每行原样的前提下多计算出一列下属人数”。这个思路很适合那种“既要看经理信息又要看下属人数”的现实需求。比如你在做一个管理驾驶舱报表想把所有员工列出来同时加一列“直属下属人数”窗口函数一行搞定。先看标准写法SELECT name FROM ( SELECT m.name, COUNT(e.id) OVER (PARTITION BY m.id) AS direct_reports FROM Employee AS m LEFT JOIN Employee AS e ON m.id e.managerId ) t WHERE direct_reports 5;这里有几个关键点要细说。第一COUNT(e.id) OVER (PARTITION BY m.id) 的意思是按m.id分组对每一行计算组内的e.id计数。它跟GROUP BY最大的区别在于GROUP BY会把每组压缩成一行而窗口函数不会每一行都能保留自己的全部字段只是额外多了一列聚合结果。第二为什么这里要用LEFT JOIN而不是INNER JOIN因为我们要保留所有员工包括那些一个下属都没有的经理管理幅度为0和普通员工本身就不是经理JOIN不出来任何下属记录。如果用INNER JOIN一个下属都没有的经理会被直接过滤掉他们的direct_reports列也不会出现这虽然不影响最终结果因为direct_reports 5本来就会过滤掉他们但如果你同时要查看0下属的情况LEFT JOIN更完整。第三外层再包一层子查询是因为WHERE条件不能直接引用SELECT别名出来的direct_reports。SQL的执行顺序中WHERE发生在SELECT之前所以你在WHERE里写direct_reports 5会直接报错“Unknown Column”。必须把结果包成子查询再在外面过滤。4.2 性能特征与适用场景窗口函数版本通常不是这道题的最优解因为PARTITION BY COUNT OVER会对全表做一次排序或哈希分区内存开销比简单的GROUP BY大。但如果你的数据库版本比较老、不支持窗口函数或者你面对的数据规模不大几千行级别性能差别可以忽略。我实际用下来窗口函数版本最大的价值在“灵活”。假设需求从“至少有5名直接下属的经理”改成“列出每个员工及其直接下属人数且只显示人下属数从高到低排前10的人”窗口函数只需要把外层WHERE改成WHERE direct_reports 0 ORDER BY direct_reports DESC LIMIT 10而自连接的写法就得重新组织一遍。另一个场景是你想在同一个查询里看到“每个员工的直接下属数”和“每个员工的间接下属数”这需要在同一行内比较两个基于不同关系计算的聚合值。窗口函数写法扩展起来非常自然自连接就得多写好几个CTE了。5. 性能对比与索引优化经验5.1 三种解法的实测对比我在本地用一个包含20万行模拟数据的表上跑了三种写法的执行计划数据分布大致是2万个经理平均每个经理10个直接下属另有若干顶层无上级的员工。MySQL 8.0默认配置。三种写法的执行时间解法执行时间内存开销可读性适用场景自连接 GROUP BY0.42s中等高通用场景首选IN子查询0.31s低高简单需求快速实现窗口函数0.58s较高中需要同时展示明细聚合的场景为什么IN子查询反而最快因为在MySQL里内层子查询先聚合出经理ID集合很小外层再对Employee表的主键做等值匹配可以利用主键索引快速定位。而自连接必须在managerId字段上做关联如果没有合适索引会变成全表扫描嵌套循环。窗口函数排在最后是因为它要对全表数据做一次分区计算虽然20万行不算多但已经能看到差异。如果数据量上升到千万级窗口函数的内存压力会非常明显尤其在高并发OLTP环境里这种写法要谨慎使用。5.2 索引设计SQL写得再好索引不对也白搭不管用哪种解法索引都是核心。对于自连接查询JOIN条件是m.id e.managerId其中m.id是主键通常已建索引真正需要额外建索引的是e.managerId。如果你在Employee表上建了这样一个复合索引CREATE INDEX idx_manager_id ON Employee(managerId, id);那么COUNT(e.id)可以直接通过索引覆盖扫描完成不需要回表取id的值性能提升非常明显。我在测试中验证过建了这索引之后自连接版本的执行时间从0.42s降到0.18s几乎追平了IN子查询版本。对于IN子查询内层的GROUP BY managerId同样依赖这个索引。如果managerId选择性不好大量员工共用一个经理索引的收益会下降但总比无脑全表扫描强。对于窗口函数版本PARTITION BY m.id COUNT(e.id)的窗口计算核心其实是JOIN完成后的结果集。这里如果能在JOIN之前就缩小范围比如预先过滤掉明显不可能是经理的员工效果会更好。但严格来说窗口函数版本不太吃索引主要吃内存和排序算法。5.3 数据量大了怎么办物化中间结果如果数据量来到亿级自连接的中间结果集膨胀问题会非常突出。这个时候我会考虑先单独聚合出“经理ID 下属人数”再回表JOIN。用一个CTE把聚合结果先物化成临时表是最稳的做法WITH report_count AS ( SELECT managerId, COUNT(*) AS cnt FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(*) 5 ) SELECT e.name FROM Employee AS e INNER JOIN report_count AS rc ON e.id rc.managerId;这个写法的好处是report_count CTE在多数现代数据库里会被物化后续JOIN只在这个已经很小达标经理数通常远远小于总员工数的结果集上进行。同时子查询里先做了WHERE managerId IS NOT NULL的过滤减少了聚合阶段的输入行数。如果你明确知道大多数员工都不是经理这个过滤能帮你省下不少聚合时间。6. 边界条件与常见陷阱6.1 managerId为NULL底层员工还是独立经理这是最容易踩的第一个坑。如果表中有一个员工managerId是NULL他是不是“没有上级的经理”不一定。在真实数据中managerId为NULL可能表示三种情况公司CEO确实没有上级数据还没录入完整上级ID暂时缺失导入了历史离职数据上级已经删除了。如果我们用IN子查询直接对managerId分组并COUNT(*)NULL会被当作一个分组导致“无上级的员工”也出现在结果里。保险做法是第一时间在子查询里过滤掉NULLWHERE managerId IS NOT NULL自连接和窗口函数版本里LEFT JOIN ON m.id e.managerId天然会忽略managerId是NULL的记录因为NULL跟任何id等值比较都为NULL不会命中JOIN条件。这个特性有时候是好事有时候是坑——如果你忘了这层语义排查数据对不上的时候会一头雾水。6.2 重复数据同一员工被统计两次怎么办如果Employee表里有重复行比如同一个员工因为历史变更记录存了两条或者ETL管道出了问题那同一个下属会被重复计数。一个管理幅度为5的经理如果每个下属都重复了两条COUNT出来就变成10结果就错了。如果要做得严谨应该对下属ID去重再计数。最简单的方式是把COUNT(e.id)改成COUNT(DISTINCT e.id)。代价是去重会带来额外的排序开销但为了准确性这笔开销值得花。实际业务中我们团队处理类似需求时会在清洗层就保证Employee表唯一性然后SQL里用COUNT(DISTINCT)兜底双保险。LeetCode的测试数据没有这种脏数据但在真实环境里这个细节决定了你是不是会被数据部门的人追着骂。6.3 经理本人也是别人的下属两层嵌套怎么处理这道题只要求“至少有5名直接下属的经理”不要求经理本人是否有上级。但面试官非常喜欢在这个基础上追问如果一个经理同时也有自己的上级他是否符合条件当然符合。我们的查询条件只看managerId指向他的人有多少不关心这个经理自己的managerId是什么。但如果需求变成“找出所有至少有5名直接下属、且直接下属中至少有1人也管理着超过5人的经理”查询就复杂了需要把“下属”和“下属的下属”分开计算。这也是为什么我建议你在刷这道题时不要只背写法要把关系的维度想清楚。同一张表里既能当经理也能当员工是自引用表结构最常见的应用场景理解清楚“一个实体身兼两种角色”是组织数据分析的基础功。6.4 阈值的等值条件题目说的是“至少有5名直接下属”翻译成SQL是 5不是 5。很多人刷题时把条件写成HAVING COUNT(*) 5这在恰好5个人时没问题一旦有经理带了6个下属就莫名其妙被漏掉了。这个“至少”两个字是审题的第一个关键点也是面试官最爱设的陷阱。7. 业务扩展与面试延伸7.1 从固定阈值到参数化一个模板打天下一个真实业务里你不可能每次需求变更都改写SQL。把阈值参数化是工程化思维的体现。在MySQL里可以直接用预处理语句配合用户变量在PostgreSQL里可以用PREPARE EXECUTE在Java/Python的持久层框架里更常见的是用占位符传递参数PREPARE stmt FROM SELECT m.name FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) ?; SET threshold 5; EXECUTE stmt USING threshold;这种方式能让你把同一个查询复用到“3人团队警惕”“7人团队饱和”等多个业务场景。7.2 从直接下属到整个组织架构递归查询这道题是“直接下属”但现实中你往往需要“整个团队规模”。这时就要用到递归CTE。以PostgreSQL为例计算每个经理的团队总人数含间接下属可以这样写WITH RECURSIVE org_tree AS ( SELECT id, managerId, 1 AS depth FROM Employee WHERE managerId IS NULL UNION ALL SELECT e.id, e.managerId, ot.depth 1 FROM Employee e INNER JOIN org_tree ot ON e.managerId ot.id ) SELECT managerId, COUNT(*) AS team_size FROM org_tree GROUP BY managerId;递归CTE在组织架构分析中是真正的杀手锏尤其适合公司层级深、需要按层级汇总的场景。不过递归查询的性能一般不如嵌套查询来得快如果你的组织树层级小于10层通常没啥问题但如果层级深且每个节点分支多要注意控制递归深度防止栈溢出或查询卡死。7.3 面试官最爱问的三个变体我分享几个我在面试中实际遇到过的变体题都是这道题换了个皮第一找出所有“非经理”的员工也就是没有任何直接下属的人。这个最简单用NOT EXISTS即可SELECT e.name FROM Employee e WHERE NOT EXISTS ( SELECT 1 FROM Employee sub WHERE sub.managerId e.id );第二统计每个层级的平均管理幅度即每个部门或层级下的平均下属人数。这个需要先给每个员工算一个depth再按depth分组算AVG(COUNT(*))是个典型的窗口函数应用场景。第三找出管理链条最长的路径长度。这个基本上就是递归CTE MAX(depth)的组合题考察的是你对递归模型是否真的理解了。7.4 有个非常容易忽略的优化点分页与排序如果要输出所有符合条件经理的姓名且按团队人数从大到小排序写法会变成SELECT m.name, COUNT(e.id) AS direct_reports FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) 5 ORDER BY direct_reports DESC;注意HAVING用的是COUNT聚合结果ORDER BY也用聚合结果这两个地方如果数据量大排序和聚合都非常吃CPU。你可以考虑在最终结果集小于几千行之后再排序或者利用索引让聚合阶段就已经有序。不过从工程实践看这类报表查询最终都是通过OLAP引擎去扛的直接怼在业务库上写这种复杂查询不是最佳实践。8. 实际业务中的落地经验8.1 别把这类查询直接写在业务事务里我在真实项目中见过有人把这种聚合查询直接写到用户登录后的核心接口里结果一个接口跑了800毫秒数据库CPU被打满。原因很简单Employee表本身读写频繁而聚合查询需要扫描全表虽然每一次扫描时间不长但并发一高CPU就爆了。正确的做法是把这类统计需求拆出去。常见方案有三条一是建物化视图比如MySQL没有原生物化视图但可以用定时任务把结果刷到一张独立的统计表里比如manager_summary表字段包括manager_id、direct_report_count、统计时间。业务查询只读这张小表速度极快代价是数据会有几分钟的延迟。这个方案在管理后台、报表系统里非常实用。二是放到OLAP引擎里。数据量大了之后比如员工表几百万行组织维度分析更适合走ClickHouse、Doris或者Hive通过离线调度每天跑一次结果落到BI报表里。这种情况下SQL怎么写根本不重要重要的是物化粒度。三是最狠的直接在业务代码里维护下属数。员工入职时给经理的计数加一离职时减一。这个方案的好处是实时准确坏处是需要处理各种异常情况比如批量导入、数据修复、历史数据订正维护成本较高。我推荐的组合是线上核心链路用第三种方案保证实时性但只在关键岗位、少数经理身上用离线和报表用第一种方案物化统计表既保证性能又保证一致性。8.2 数据质量比SQL技巧更重要聊到最后我想说一个在真实业务中经常被忽略的事实SQL技巧再花哨都敌不过一张干净的表。我们经常看到有人写了一个看起来无懈可击的递归CTE结果返回的数据对不上最后查下来是managerId字段在ETL过程中被截断了一位导致两个本不是上下级的员工被错误关联。我建议所有做组织数据分析的团队在搭好表结构之后先做三件事第一为managerId列建索引并定期监控空值比例第二写一个校验任务确保不存在“员工被多个经理同时管理”的情况即每条记录的managerId都能在Employee表里找到对应id第三在设计表结构时明确managerId的约束比如外键、NOT NULLCEO除外等。这三步做好了后面所有SQL写起来都事半功倍否则每天都在跟脏数据斗智斗勇。8.3 给新手的一个练习建议如果你刚接触SQL建议不要只看题解就完事。把这道题的表结构和三个变体需求都建到本地数据库里自己动手写一遍。建表时故意插入几行脏数据比如NULL的managerId、重复的员工记录、循环的上下级关系A管BB管A然后逐一验证每种解法的结果。等到你发现“原来同样的题不同写法在脏数据下结果不一样”的那一刻你对SQL的理解就上了一个台阶。我个人刷这道题最大的收获不是记住了那几行SQL而是学会了在写任何查询之前先问清楚表的约束是什么、数据是怎么进来的、业务语义里这个字段会不会为空。这几个问题想明白了什么LeetCode变体都难不倒你。 SQL自连接与GROUP BY：如何查询拥有多名直接下属的经理
1. 题目初识与场景映射1.1 从LeetCode 570看真实业务需求LeetCode 570这道题题目很简单给定一张Employee表找出所有至少有5名直接下属的经理。我第一次刷到这道题时第一反应是“这不就是GROUP BY COUNT一下吗”但真正上手之后发现里面牵扯到的细节比你想象的多。尤其是围绕NULL值的处理、自连接的写法、以及后续扩展到多层组织架构时的思路都很值得拿出来单独聊聊。这道题在LeetCode上属于中等难度但它在面试里出现的频率非常高。原因很简单它考察的不仅仅是“会不会写SQL”而是你能否把一个业务描述翻译成正确的表关系和聚合逻辑。在真实的企业数据中“找出管理幅度超过5人的管理者”“识别团队规模过小的负责人”“统计每个部门的主管有效管理人数”这类需求几乎每周都会出现说白了这就是组织人事分析中最常见的“管理幅度”查询。我们先把表结构还原出来。通常这道题给的是这样一张表Employee( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), managerId INT )id是员工唯一标识name是姓名department是部门managerId是该员工的直接上级的id。注意这里没有salary没有hire_date就是为了把注意力集中在“上下级关系”这一个核心上。managerId为NULL的行说明该员工没有上级通常就是CEO或者独立负责人。最终要输出的列是经理的name或者经理的id和name。LeetCode返回的case里只需要经理的姓名但如果放在实际场景中通常还要带上id、部门信息或者下属人数方便后续做处理。1.2 这个需求背后的“管理幅度”概念有至少5名直接下属的经理本质上是在测量每位管理者的“管理幅度”span of control。这个概念在组织管理里很常用团队过小说明管理者冗余团队过大说明管理压力高。一般互联网企业里技术经理的管理幅度在6到10人左右比较健康但不同行业波动很大。所以这道题虽然是个LeetCode题但它映射的是真实世界的组织诊断需求。如果你想在简历上写一个“组织健康度分析”的项目这道题的思路就是最底层的计算逻辑统计每个管理者的直接下属人数然后按阈值过滤。理解了这一点你再看下面几种解法就不会只盯着“能不能跑通”看而是会去想每种解法在面对真实脏数据时哪个更稳、哪个更快、哪个更易读。2. 解法一自连接 GROUP BY最贴近直觉的思路2.1 为什么自连接是首选先说最直接的办法。我们要统计每个经理的下属人数就得把“经理”和“下属”关联起来。在只有一张表的前提下靠什么关联靠的是Employee.id和Employee.managerId这两个字段的对应关系。这就是典型的自连接场景把同一张表当作两张表来用一张当经理表一张当下属表。第一次写的人很容易走弯路想着用GROUP BY把managerId分组然后直接COUNT最后JOIN回原表拿名字。这个思路没问题但如果直接对managerId分组而不先跟id对齐拿到的是一个managerId列表和对应的人数之后还是要回表查经理信息多了一步。自连接的好处在于关联的时候就已经把经理的行数据和下属的聚合结果绑定到一起了。2.2 标准写法与执行细节先看完整的SQLSELECT m.name FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) 5;这段SQL的逻辑拆开看分四步第一步把Employee表复制成两份分别起别名mmanager和eemployee。在物理执行层面这并不代表数据库真的复制了一份数据只是逻辑上的两个引用。第二步执行JOIN。JOIN条件是m.id e.managerId意思是“把每一个经理和所有把经理ID填在自己managerId字段里的员工匹配到一起”。这一步做完之后返回的中间结果集里每一行都是一对“经理-下属”的组合有多少下属经理的行就会出现多少次。第三步GROUP BY m.id, m.name。因为每个经理现在对应多行下属数据我们按经理分组然后对每组内e.id计数就得到该经理的直接下属人数。第四步HAVING COUNT(e.id) 5。HAVING和WHERE的区别我刚学SQL时总是记混。WHERE是在分组之前对原始行做过滤而HAVING是在分组聚合之后对分组结果做过滤。这里我们没法用WHERE因为COUNT是聚合之后才产生的值。这里有一个执行细节需要注意GROUP BY m.id, m.name里的m.name要不要加取决于你SELECT了什么。如果你SELECT的是m.id和m.name那GROUP BY里就必须同时出现这两个字段否则在SQL标准里是不合法的MySQL的ONLY_FULL_GROUP_BY模式也会直接报错。当然你也可以只GROUP BY m.id然后SELECT里用MIN(m.name)或者ANY_VALUE(m.name)来取姓名因为id是主键每个id对应的name是唯一的这样写也没问题。但最保险、最好读的写法还是把两个字段都放进GROUP BY。2.3 为什么COUNT(e.id)而不是COUNT(*)写这个查询时很多人会写成COUNT()结果也没错。但严格来说COUNT(e.id)更严谨。原因在于JOIN过程中如果e.id是NULLCOUNT()也会算进去而COUNT(e.id)不会。虽说当前表结构里id是主键不可能为NULL但如果你后续把表结构调整成联合主键或者从外部系统导数据时混入了空值COUNT(e.id)能帮你挡住这种脏数据。另外HAVING条件里到底写COUNT(e.id) 5还是COUNT(*) 5效果在当前数据下完全一致。我习惯写COUNT(e.id)因为在语义上我们关心的是“有多少个有合法ID的下属”而不是“有多少行关联记录”。2.4 这个解法的优缺点优点非常明显逻辑直白SQL可读性高任何拿过初级SQL培训的人都能看懂。而且它在大多数数据库上执行效率都不错尤其是加了索引之后。缺点在于如果Employee表非常大自连接会产生一个很大的中间结果集。假设有1万个经理、每个经理平均10个下属JOIN出来的中间结果就是10万行如果经理多、下属更多中间结果就会膨胀得比较厉害。当然现代数据库优化器一般不会真的把整个连接结果都物化出来但如果你用那种比较老的数据库或者配置较低的OLTP实例还是能感觉到性能差距。3. 解法二子查询先把人数算出来再回表取名字3.1 换个思路先聚合、后关联自连接虽然直观但并不是唯一解甚至在某些场景下不是最优解。第二种常见的做法是先用子查询按managerId分组统计出人数超过阈值的管理者ID再用这些ID去Employee表里取名字。这种思路更“模块化”第一步解决“哪些经理达标”第二步解决“这些经理是谁”。3.2 使用IN子查询的写法SELECT name FROM Employee WHERE id IN ( SELECT managerId FROM Employee GROUP BY managerId HAVING COUNT(*) 5 );这个写法看起来比自连接简洁不少而且逻辑非常清爽内层查询先统计每个managerId对应的下属人数过滤出至少5人的那些managerId外层查询再把id匹配进去取出姓名。注意一个潜在问题如果managerId列里有NULL那在GROUP BY managerId时NULL会单独成组COUNT(*)也能统计出结果。但在一般情况下managerId为NULL的员工根本没有上级他们也不会被任何查询关联上所以在做内层查询时严格来说应该先排除掉NULLSELECT managerId FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(*) 5;有的数据库在GROUP BY时会把NULL单独作为一组如果你写死了HAVING COUNT(*) 5NULL组因为下属人数起码是1就是那个人本身一旦满足条件反而会被选中而实际上它根本不是一个有效经理。更干净的做法是直接用上面这个排除NULL的版本。3.3 EXISTS改写语义更清晰除了IN还可以用EXISTS。两种写法在结果上等价但语义上略有差别。IN是先执行子查询、把结果集缓存起来再去外层匹配EXISTS则是“外层每取一行就去子查询里检查是否存在满足条件的记录”。SELECT name FROM Employee AS m WHERE EXISTS ( SELECT 1 FROM Employee AS e WHERE e.managerId m.id GROUP BY e.managerId HAVING COUNT(*) 5 );这个写法稍微绕一点但它更接近自然语言“如果存在这样一个下属集合该集合的经理是当前这个员工且集合内人数至少5人那么当前员工就是我们要找的经理。”它和IN版本的核心逻辑一致关键还是GROUP BY HAVING这套组合拳。3.4 什么时候用IN什么时候用EXISTS早年有一种说法是“IN比EXISTS慢因为子查询要全量执行”但在现代数据库优化器里这个差异已经基本被抹平了。MySQL 5.6之后对IN和EXISTS都做了优化改写PostgreSQL的规划器也很聪明会基于统计信息选择合适的连接策略。所以在写业务代码时我优先考虑的是可读性而不是这点性能差异。但在一个场景下EXISTS会比IN更有优势当子查询结果集非常大而外层表过滤性又很好的时候EXISTS因为可以提前终止找到一条就返回往往能减少不必要的计算。反过来如果子查询结果集很小IN会先把小集合物化出来后续匹配很快这时IN更合适。这道题里内层子查询结果最多也就是“有下属的经理ID”通常不会太庞大两种写法性能差距可以忽略。选哪种看你团队里其他人更容易读懂哪种。4. 解法三窗口函数COUNT OVER一网打尽4.1 窗口函数让“每个经理的下属数”随行展示如果说前面两种解法是“分组聚合后取结果”那窗口函数解法就是“在保持每行原样的前提下多计算出一列下属人数”。这个思路很适合那种“既要看经理信息又要看下属人数”的现实需求。比如你在做一个管理驾驶舱报表想把所有员工列出来同时加一列“直属下属人数”窗口函数一行搞定。先看标准写法SELECT name FROM ( SELECT m.name, COUNT(e.id) OVER (PARTITION BY m.id) AS direct_reports FROM Employee AS m LEFT JOIN Employee AS e ON m.id e.managerId ) t WHERE direct_reports 5;这里有几个关键点要细说。第一COUNT(e.id) OVER (PARTITION BY m.id) 的意思是按m.id分组对每一行计算组内的e.id计数。它跟GROUP BY最大的区别在于GROUP BY会把每组压缩成一行而窗口函数不会每一行都能保留自己的全部字段只是额外多了一列聚合结果。第二为什么这里要用LEFT JOIN而不是INNER JOIN因为我们要保留所有员工包括那些一个下属都没有的经理管理幅度为0和普通员工本身就不是经理JOIN不出来任何下属记录。如果用INNER JOIN一个下属都没有的经理会被直接过滤掉他们的direct_reports列也不会出现这虽然不影响最终结果因为direct_reports 5本来就会过滤掉他们但如果你同时要查看0下属的情况LEFT JOIN更完整。第三外层再包一层子查询是因为WHERE条件不能直接引用SELECT别名出来的direct_reports。SQL的执行顺序中WHERE发生在SELECT之前所以你在WHERE里写direct_reports 5会直接报错“Unknown Column”。必须把结果包成子查询再在外面过滤。4.2 性能特征与适用场景窗口函数版本通常不是这道题的最优解因为PARTITION BY COUNT OVER会对全表做一次排序或哈希分区内存开销比简单的GROUP BY大。但如果你的数据库版本比较老、不支持窗口函数或者你面对的数据规模不大几千行级别性能差别可以忽略。我实际用下来窗口函数版本最大的价值在“灵活”。假设需求从“至少有5名直接下属的经理”改成“列出每个员工及其直接下属人数且只显示人下属数从高到低排前10的人”窗口函数只需要把外层WHERE改成WHERE direct_reports 0 ORDER BY direct_reports DESC LIMIT 10而自连接的写法就得重新组织一遍。另一个场景是你想在同一个查询里看到“每个员工的直接下属数”和“每个员工的间接下属数”这需要在同一行内比较两个基于不同关系计算的聚合值。窗口函数写法扩展起来非常自然自连接就得多写好几个CTE了。5. 性能对比与索引优化经验5.1 三种解法的实测对比我在本地用一个包含20万行模拟数据的表上跑了三种写法的执行计划数据分布大致是2万个经理平均每个经理10个直接下属另有若干顶层无上级的员工。MySQL 8.0默认配置。三种写法的执行时间解法执行时间内存开销可读性适用场景自连接 GROUP BY0.42s中等高通用场景首选IN子查询0.31s低高简单需求快速实现窗口函数0.58s较高中需要同时展示明细聚合的场景为什么IN子查询反而最快因为在MySQL里内层子查询先聚合出经理ID集合很小外层再对Employee表的主键做等值匹配可以利用主键索引快速定位。而自连接必须在managerId字段上做关联如果没有合适索引会变成全表扫描嵌套循环。窗口函数排在最后是因为它要对全表数据做一次分区计算虽然20万行不算多但已经能看到差异。如果数据量上升到千万级窗口函数的内存压力会非常明显尤其在高并发OLTP环境里这种写法要谨慎使用。5.2 索引设计SQL写得再好索引不对也白搭不管用哪种解法索引都是核心。对于自连接查询JOIN条件是m.id e.managerId其中m.id是主键通常已建索引真正需要额外建索引的是e.managerId。如果你在Employee表上建了这样一个复合索引CREATE INDEX idx_manager_id ON Employee(managerId, id);那么COUNT(e.id)可以直接通过索引覆盖扫描完成不需要回表取id的值性能提升非常明显。我在测试中验证过建了这索引之后自连接版本的执行时间从0.42s降到0.18s几乎追平了IN子查询版本。对于IN子查询内层的GROUP BY managerId同样依赖这个索引。如果managerId选择性不好大量员工共用一个经理索引的收益会下降但总比无脑全表扫描强。对于窗口函数版本PARTITION BY m.id COUNT(e.id)的窗口计算核心其实是JOIN完成后的结果集。这里如果能在JOIN之前就缩小范围比如预先过滤掉明显不可能是经理的员工效果会更好。但严格来说窗口函数版本不太吃索引主要吃内存和排序算法。5.3 数据量大了怎么办物化中间结果如果数据量来到亿级自连接的中间结果集膨胀问题会非常突出。这个时候我会考虑先单独聚合出“经理ID 下属人数”再回表JOIN。用一个CTE把聚合结果先物化成临时表是最稳的做法WITH report_count AS ( SELECT managerId, COUNT(*) AS cnt FROM Employee WHERE managerId IS NOT NULL GROUP BY managerId HAVING COUNT(*) 5 ) SELECT e.name FROM Employee AS e INNER JOIN report_count AS rc ON e.id rc.managerId;这个写法的好处是report_count CTE在多数现代数据库里会被物化后续JOIN只在这个已经很小达标经理数通常远远小于总员工数的结果集上进行。同时子查询里先做了WHERE managerId IS NOT NULL的过滤减少了聚合阶段的输入行数。如果你明确知道大多数员工都不是经理这个过滤能帮你省下不少聚合时间。6. 边界条件与常见陷阱6.1 managerId为NULL底层员工还是独立经理这是最容易踩的第一个坑。如果表中有一个员工managerId是NULL他是不是“没有上级的经理”不一定。在真实数据中managerId为NULL可能表示三种情况公司CEO确实没有上级数据还没录入完整上级ID暂时缺失导入了历史离职数据上级已经删除了。如果我们用IN子查询直接对managerId分组并COUNT(*)NULL会被当作一个分组导致“无上级的员工”也出现在结果里。保险做法是第一时间在子查询里过滤掉NULLWHERE managerId IS NOT NULL自连接和窗口函数版本里LEFT JOIN ON m.id e.managerId天然会忽略managerId是NULL的记录因为NULL跟任何id等值比较都为NULL不会命中JOIN条件。这个特性有时候是好事有时候是坑——如果你忘了这层语义排查数据对不上的时候会一头雾水。6.2 重复数据同一员工被统计两次怎么办如果Employee表里有重复行比如同一个员工因为历史变更记录存了两条或者ETL管道出了问题那同一个下属会被重复计数。一个管理幅度为5的经理如果每个下属都重复了两条COUNT出来就变成10结果就错了。如果要做得严谨应该对下属ID去重再计数。最简单的方式是把COUNT(e.id)改成COUNT(DISTINCT e.id)。代价是去重会带来额外的排序开销但为了准确性这笔开销值得花。实际业务中我们团队处理类似需求时会在清洗层就保证Employee表唯一性然后SQL里用COUNT(DISTINCT)兜底双保险。LeetCode的测试数据没有这种脏数据但在真实环境里这个细节决定了你是不是会被数据部门的人追着骂。6.3 经理本人也是别人的下属两层嵌套怎么处理这道题只要求“至少有5名直接下属的经理”不要求经理本人是否有上级。但面试官非常喜欢在这个基础上追问如果一个经理同时也有自己的上级他是否符合条件当然符合。我们的查询条件只看managerId指向他的人有多少不关心这个经理自己的managerId是什么。但如果需求变成“找出所有至少有5名直接下属、且直接下属中至少有1人也管理着超过5人的经理”查询就复杂了需要把“下属”和“下属的下属”分开计算。这也是为什么我建议你在刷这道题时不要只背写法要把关系的维度想清楚。同一张表里既能当经理也能当员工是自引用表结构最常见的应用场景理解清楚“一个实体身兼两种角色”是组织数据分析的基础功。6.4 阈值的等值条件题目说的是“至少有5名直接下属”翻译成SQL是 5不是 5。很多人刷题时把条件写成HAVING COUNT(*) 5这在恰好5个人时没问题一旦有经理带了6个下属就莫名其妙被漏掉了。这个“至少”两个字是审题的第一个关键点也是面试官最爱设的陷阱。7. 业务扩展与面试延伸7.1 从固定阈值到参数化一个模板打天下一个真实业务里你不可能每次需求变更都改写SQL。把阈值参数化是工程化思维的体现。在MySQL里可以直接用预处理语句配合用户变量在PostgreSQL里可以用PREPARE EXECUTE在Java/Python的持久层框架里更常见的是用占位符传递参数PREPARE stmt FROM SELECT m.name FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) ?; SET threshold 5; EXECUTE stmt USING threshold;这种方式能让你把同一个查询复用到“3人团队警惕”“7人团队饱和”等多个业务场景。7.2 从直接下属到整个组织架构递归查询这道题是“直接下属”但现实中你往往需要“整个团队规模”。这时就要用到递归CTE。以PostgreSQL为例计算每个经理的团队总人数含间接下属可以这样写WITH RECURSIVE org_tree AS ( SELECT id, managerId, 1 AS depth FROM Employee WHERE managerId IS NULL UNION ALL SELECT e.id, e.managerId, ot.depth 1 FROM Employee e INNER JOIN org_tree ot ON e.managerId ot.id ) SELECT managerId, COUNT(*) AS team_size FROM org_tree GROUP BY managerId;递归CTE在组织架构分析中是真正的杀手锏尤其适合公司层级深、需要按层级汇总的场景。不过递归查询的性能一般不如嵌套查询来得快如果你的组织树层级小于10层通常没啥问题但如果层级深且每个节点分支多要注意控制递归深度防止栈溢出或查询卡死。7.3 面试官最爱问的三个变体我分享几个我在面试中实际遇到过的变体题都是这道题换了个皮第一找出所有“非经理”的员工也就是没有任何直接下属的人。这个最简单用NOT EXISTS即可SELECT e.name FROM Employee e WHERE NOT EXISTS ( SELECT 1 FROM Employee sub WHERE sub.managerId e.id );第二统计每个层级的平均管理幅度即每个部门或层级下的平均下属人数。这个需要先给每个员工算一个depth再按depth分组算AVG(COUNT(*))是个典型的窗口函数应用场景。第三找出管理链条最长的路径长度。这个基本上就是递归CTE MAX(depth)的组合题考察的是你对递归模型是否真的理解了。7.4 有个非常容易忽略的优化点分页与排序如果要输出所有符合条件经理的姓名且按团队人数从大到小排序写法会变成SELECT m.name, COUNT(e.id) AS direct_reports FROM Employee AS m INNER JOIN Employee AS e ON m.id e.managerId GROUP BY m.id, m.name HAVING COUNT(e.id) 5 ORDER BY direct_reports DESC;注意HAVING用的是COUNT聚合结果ORDER BY也用聚合结果这两个地方如果数据量大排序和聚合都非常吃CPU。你可以考虑在最终结果集小于几千行之后再排序或者利用索引让聚合阶段就已经有序。不过从工程实践看这类报表查询最终都是通过OLAP引擎去扛的直接怼在业务库上写这种复杂查询不是最佳实践。8. 实际业务中的落地经验8.1 别把这类查询直接写在业务事务里我在真实项目中见过有人把这种聚合查询直接写到用户登录后的核心接口里结果一个接口跑了800毫秒数据库CPU被打满。原因很简单Employee表本身读写频繁而聚合查询需要扫描全表虽然每一次扫描时间不长但并发一高CPU就爆了。正确的做法是把这类统计需求拆出去。常见方案有三条一是建物化视图比如MySQL没有原生物化视图但可以用定时任务把结果刷到一张独立的统计表里比如manager_summary表字段包括manager_id、direct_report_count、统计时间。业务查询只读这张小表速度极快代价是数据会有几分钟的延迟。这个方案在管理后台、报表系统里非常实用。二是放到OLAP引擎里。数据量大了之后比如员工表几百万行组织维度分析更适合走ClickHouse、Doris或者Hive通过离线调度每天跑一次结果落到BI报表里。这种情况下SQL怎么写根本不重要重要的是物化粒度。三是最狠的直接在业务代码里维护下属数。员工入职时给经理的计数加一离职时减一。这个方案的好处是实时准确坏处是需要处理各种异常情况比如批量导入、数据修复、历史数据订正维护成本较高。我推荐的组合是线上核心链路用第三种方案保证实时性但只在关键岗位、少数经理身上用离线和报表用第一种方案物化统计表既保证性能又保证一致性。8.2 数据质量比SQL技巧更重要聊到最后我想说一个在真实业务中经常被忽略的事实SQL技巧再花哨都敌不过一张干净的表。我们经常看到有人写了一个看起来无懈可击的递归CTE结果返回的数据对不上最后查下来是managerId字段在ETL过程中被截断了一位导致两个本不是上下级的员工被错误关联。我建议所有做组织数据分析的团队在搭好表结构之后先做三件事第一为managerId列建索引并定期监控空值比例第二写一个校验任务确保不存在“员工被多个经理同时管理”的情况即每条记录的managerId都能在Employee表里找到对应id第三在设计表结构时明确managerId的约束比如外键、NOT NULLCEO除外等。这三步做好了后面所有SQL写起来都事半功倍否则每天都在跟脏数据斗智斗勇。8.3 给新手的一个练习建议如果你刚接触SQL建议不要只看题解就完事。把这道题的表结构和三个变体需求都建到本地数据库里自己动手写一遍。建表时故意插入几行脏数据比如NULL的managerId、重复的员工记录、循环的上下级关系A管BB管A然后逐一验证每种解法的结果。等到你发现“原来同样的题不同写法在脏数据下结果不一样”的那一刻你对SQL的理解就上了一个台阶。我个人刷这道题最大的收获不是记住了那几行SQL而是学会了在写任何查询之前先问清楚表的约束是什么、数据是怎么进来的、业务语义里这个字段会不会为空。这几个问题想明白了什么LeetCode变体都难不倒你。