在单表中运行多COUNT/统计语句实现校/区员工数据对比的最优方案

嘿，我明白你的需求了——你现在有一个能查单个学区单个学校员工统计的SQL语句，想要一次性对比多所学校甚至多个学区的数据，但因为所有数据都在一张表里，不想重复写一堆几乎一样的SELECT语句对吧？这几个SQL方案应该能帮到你：

方法1：条件聚合（Case When + 聚合函数）—— 横向对比首选 #

这种方式能把不同学校/学区的统计结果放到同一行的不同列，直接实现横向对比，非常直观。

假设你的原查询是这样的：

SELECT 
  COUNT(employee_id) AS total_staff,
  SUM(salary) AS total_salary
FROM employee_stats
WHERE school_id = 'XXX' AND district_id = 'YYY';

要对比同一学区的A、B两所学校，就可以改成：

SELECT 
  district_id,
  -- 学校A的统计项
  COUNT(CASE WHEN school_id = 'A' THEN employee_id END) AS school_A_total_staff,
  SUM(CASE WHEN school_id = 'A' THEN salary END) AS school_A_total_salary,
  -- 学校B的统计项
  COUNT(CASE WHEN school_id = 'B' THEN employee_id END) AS school_B_total_staff,
  SUM(CASE WHEN school_id = 'B' THEN salary END) AS school_B_total_salary
FROM employee_stats
WHERE district_id = 'YYY'
GROUP BY district_id;

如果要跨学区对比，只需调整条件即可：

SELECT 
  COUNT(CASE WHEN district_id = 'X' AND school_id = 'A' THEN employee_id END) AS districtX_schoolA_total_staff,
  COUNT(CASE WHEN district_id = 'Y' AND school_id = 'B' THEN employee_id END) AS districtY_schoolB_total_staff
FROM employee_stats;

方法2：GROUP BY分组——纵向对比更灵活 #

如果想把每个学校/学区的统计结果作为单独行展示，用GROUP BY分组是最直接的方式，适合做纵向对比：

SELECT 
  district_id,
  school_id,
  COUNT(employee_id) AS total_staff,
  SUM(salary) AS total_salary
FROM employee_stats
-- 按需筛选要对比的对象，比如指定学区或学校
WHERE district_id IN ('X','Y') OR school_id IN ('A','B')
GROUP BY district_id, school_id
ORDER BY district_id, school_id;

查询结果会按学区+学校分组，每一行对应一个学校的统计数据，方便批量查看和对比。

方法3：WITH子句（CTE）——复杂逻辑更易维护 #

如果你的统计逻辑包含很多计算、筛选条件，不想重复写多次，可以用CTE封装通用逻辑，再基于CTE筛选对比对象：

WITH school_stats AS (
  -- 这里放你原有的统计逻辑，去掉具体的school_id/district_id过滤
  SELECT 
    district_id,
    school_id,
    COUNT(employee_id) AS total_staff,
    SUM(salary) AS total_salary,
    AVG(age) AS avg_employee_age -- 其他统计字段
  FROM employee_stats
  -- 保留通用过滤条件，比如统计2024年入职的员工
  WHERE hire_year >= 2024
  GROUP BY district_id, school_id
)
-- 从CTE中筛选需要对比的目标
SELECT *
FROM school_stats
WHERE (district_id = 'X' AND school_id IN ('A','B')) 
   OR (district_id = 'Y' AND school_id = 'C');

这种方式让代码结构更清晰，后续修改统计逻辑时，只需调整CTE部分即可，维护成本更低。

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