如何按自定义的work experience列对SQL查询结果进行分组？

先看你的SQL语句：

SELECT 
    CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Name and Ln",
    MONTHS_BETWEEN(SYSDATE, hire_date) / 12 AS "work experience"
FROM 
    HR.EMPLOYEES 
WHERE
    MONTHS_BETWEEN(SYSDATE, hire_date) >= 15 * 12 

针对你的问题：不能直接用GROUP BY "work experience"。原因是你用的HR.EMPLOYEES是Oracle的示例表，而Oracle的SQL解析顺序是先处理FROM/WHERE，再执行GROUP BY，最后才处理SELECT里的列别名——也就是说GROUP BY阶段还不知道"work experience"这个别名对应的计算逻辑，直接引用会报错。

解决方法有两种常用方案：

• 方案一：在GROUP BY中重复写自定义列的计算表达式

  SELECT 
      CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Name and Ln",
      MONTHS_BETWEEN(SYSDATE, hire_date) / 12 AS "work experience",
      COUNT(*) AS employee_count -- 这里是示例聚合函数，可替换为你需要的统计逻辑
  FROM 
      HR.EMPLOYEES 
  WHERE
      MONTHS_BETWEEN(SYSDATE, hire_date) >= 15 * 12 
  GROUP BY 
      CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME),
      MONTHS_BETWEEN(SYSDATE, hire_date) / 12
  

  注意：GROUP BY必须包含SELECT中所有未被聚合的列（比如这里的员工姓名对应的表达式），否则会触发语法错误。

• 方案二：用子查询或CTE提前计算出自定义列，再在外层分组
  用CTE的示例（可读性更强，适合复杂计算逻辑）：

  WITH employee_exp AS (
      SELECT 
          CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Name and Ln",
          MONTHS_BETWEEN(SYSDATE, hire_date) / 12 AS "work experience"
      FROM 
          HR.EMPLOYEES 
      WHERE
          MONTHS_BETWEEN(SYSDATE, hire_date) >= 15 * 12 
  )
  SELECT 
      "Name and Ln",
      "work experience",
      COUNT(*) AS employee_count
  FROM employee_exp
  GROUP BY "Name and Ln", "work experience"
  

如果你的需求是按工作经验区间分组（比如15-20年、20-25年这类分段），可以先通过CASE语句处理分段，再按分段后的列分组，示例如下：

SELECT 
    CASE 
        WHEN "work experience" BETWEEN 15 AND 20 THEN '15-20年'
        WHEN "work experience" BETWEEN 20 AND 25 THEN '20-25年'
        ELSE '25年以上'
    END AS exp_range,
    COUNT(*) AS employee_count
FROM (
    SELECT 
        MONTHS_BETWEEN(SYSDATE, hire_date) / 12 AS "work experience"
    FROM HR.EMPLOYEES 
    WHERE MONTHS_BETWEEN(SYSDATE, hire_date) >= 15 * 12
)
GROUP BY 
    CASE 
        WHEN "work experience" BETWEEN 15 AND 20 THEN '15-20年'
        WHEN "work experience" BETWEEN 20 AND 25 THEN '20-25年'
        ELSE '25年以上'
    END

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