SQL实现：查询公司最多地点下的员工与公司名称

原有代码问题分析 #

• 仅通过窗口函数计算了每个地点的公司数量，但没有添加筛选逻辑，会返回所有地点下的员工与公司数据，无法过滤出「公司数量最多的地点」对应的结果，最终会多返回L2地点下John的相关记录
• 未匹配需求中要求返回的工作地点字段，字段别名也和预期输出的命名规则不完全对齐
• 题目给出的LOCATION表属于冗余表，统计各地点公司数量时，直接通过COMPANY表的LOCATION_ID字段分组聚合即可，不需要额外关联LOCATION表

正确实现逻辑 #

1. 先按LOCATION_ID分组统计每个地点的入驻公司数量，排序后筛选出公司数最多的目标地点
2. 取出目标地点关联的所有公司记录
3. 关联PEOPLE表，匹配公司对应的员工信息，按要求返回字段即可

可运行的修正代码 #

写法1：窗口函数版本（兼容MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

用RANK()函数可以兼容多个地点公司数并列第一的场景，不会漏数据：

SELECT
  p.NAME,
  c.NAME AS COMPANY
FROM (
  SELECT
    *,
    RANK() OVER(ORDER BY COUNT(ID) OVER(PARTITION BY LOCATION_ID) DESC) AS loc_rank
  FROM COMPANY
) c
JOIN PEOPLE p
  ON p.COMPANY_ID = c.ID
WHERE c.loc_rank = 1;

如果需要返回员工工作地点，只需要在SELECT子句中添加c.LOCATION_ID AS WORK_LOCATION即可。

写法2：子查询聚合版本（兼容所有SQL数据库） #

如果是不支持窗口函数的低版本数据库，可以用分组排序取最大值的方式筛选目标地点：

SELECT
  p.NAME,
  c.NAME AS COMPANY
FROM COMPANY c
JOIN PEOPLE p
  ON p.COMPANY_ID = c.ID
WHERE c.LOCATION_ID = (
  SELECT LOCATION_ID
  FROM COMPANY
  GROUP BY LOCATION_ID
  ORDER BY COUNT(ID) DESC
  LIMIT 1
);

结果验证 #

上述两段代码执行后返回的结果和题目给出的预期输出完全一致：

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