如何修改SQL查询按性别统计不同服务年限区间的员工人数

我来帮你调整SQL语句，满足按性别分组、统计不同服务年限区间内各性别人数的需求~

修改后的SQL语句 #

SELECT 
  (CASE 
     WHEN service_year <= 2 THEN '0-2' 
     WHEN service_year BETWEEN 3 AND 5 THEN '3-5' 
     WHEN service_year BETWEEN 6 AND 9 THEN '6-9' 
     WHEN service_year > 9 THEN '>9' 
     WHEN service_year IS NULL THEN '(NULL)' 
   END) AS range_service,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  COUNT(*) AS total_count
FROM (
  SELECT 
    gender, 
    TIMESTAMPDIFF(YEAR, join_date, CURDATE()) AS service_year 
  FROM employee
) AS dummy_table
GROUP BY range_service
ORDER BY 
  CASE range_service 
    WHEN '0-2' THEN 1 
    WHEN '3-5' THEN 2 
    WHEN '6-9' THEN 3 
    WHEN '>9' THEN 4 
    WHEN '(NULL)' THEN 5 
  END;

关键调整说明 #

• 按性别拆分统计：通过COUNT(CASE WHEN gender = 'M' THEN 1 END)这种条件聚合方式，分别统计每个服务年限区间里男性和女性的人数。当性别匹配时返回1，不匹配时返回NULL，而COUNT函数会自动忽略NULL值，刚好得到对应性别的人数。
• 修复排序逻辑：原来直接用ORDER BY range_service会因为字符串排序规则导致顺序混乱（比如'>9'会排在'0-2'前面），新增的CASE语句给每个区间指定了排序优先级，确保结果按0-2 → 3-5 → 6-9 → >9 → (NULL)的顺序展示。

如果你的gender字段取值不是'M'和'F'，记得把CASE里的判断值改成实际的性别标识哦~

内容的提问来源于stack exchange，提问作者Excel Dwi Oktavianto