如何对子查询中的列进行分组？SQL子查询分组问题求助

问题背景 #

现有查询语句用于关联员工表与奖金表，计算员工带奖金的薪资：

SELECT 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
    ((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id) + bonus_sum) as sum_plus_bonus
FROM
    (SELECT worker_ref_id, SUM(bonus) as bonus_sum
    FROM sf_bonus
    GROUP BY worker_ref_id) AS T1

当前仅能通过将上述查询嵌套为子查询（命名为T2），再按employee_title和sex分组计算平均值，虽可行但代码冗余：

SELECT employee_title, sex, AVG(sum_plus_bonus) AS avg_salary
FROM
    (SELECT 
        (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
        (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
        ((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id) + bonus_sum) as sum_plus_bonus
    FROM
        (SELECT worker_ref_id, SUM(bonus) as bonus_sum
        FROM sf_bonus
        GROUP BY worker_ref_id) AS T1) AS T2
GROUP BY employee_title, sex

尝试直接在第一层查询中添加AVG()和GROUP BY时报错，代码如下：

SELECT 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
    AVG((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id) + bonus_sum) as sum_plus_bonus
FROM
    (SELECT worker_ref_id, SUM(bonus) as bonus_sum
    FROM sf_bonus
    GROUP BY worker_ref_id) AS T1
GROUP BY employee_title, sex

需求：不嵌套多层子查询，仅用子查询完成分组计算平均值，且禁止使用JOIN。

解决方案 #

报错原因 #

部分SQL引擎不允许在同一层级的SELECT和GROUP BY中直接引用标量子查询生成的别名，导致语法解析失败。

修正后的代码 #

SELECT 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id) AS employee_title,
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id) AS sex,
    AVG((SELECT salary FROM sf_employee WHERE id = T1.worker_ref_id) + T1.bonus_sum) AS avg_salary
FROM
    (SELECT worker_ref_id, SUM(bonus) AS bonus_sum
     FROM sf_bonus
     GROUP BY worker_ref_id) AS T1
GROUP BY 
    (SELECT employee_title FROM sf_employee WHERE id = T1.worker_ref_id),
    (SELECT sex FROM sf_employee WHERE id = T1.worker_ref_id)

关键说明 #

1. 替代别名引用：在GROUP BY中直接重复子查询语句，避免SQL引擎对别名的解析限制
2. 逻辑一致性：AVG()先计算单个员工的薪资加总奖金，再按职位、性别分组求平均，与原嵌套方法逻辑完全一致
3. 满足练习要求：全程仅使用子查询实现，未使用JOIN类语法

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