如何对子查询列进行分组?

Question

我有查询:

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

和我知道如何进行分组的唯一方法是将此表作为FROM中的T2表中的子查询，然后按工资和奖金分组并找到第一列和第二列的平均值:

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

它工作，但我没有经验，所以它看起来有点奇怪对我来说，我想我可以不添加代码在第二个选项。我对join和其他一些函数不感兴趣，我正在训练子查询对使用它们有信心，如果有人对任务感兴趣，这里是:https://platform.stratascratch.com/coding/10077-income-by-title-and-gender?code_type=5

这是我想做的，我得到了错误:

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

i add AVG and GROUP BY

Answer 1

您可以将SELECT中的表链接到FROMsection中，并将其分组为，而不包含附加subquery.

SELECT 
se.employee_title, se.sex, AVG(se.salary + 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
LEFT JOIN sf_employee AS se on se.id = T1.worker_ref_id
GROUP BY
se.employee_title, se.sex

Answer 2

WITH子句在这里只是生成一些示例数据，因此，它不是答案的一部分:

WITH
sf_employee (ID, FIST_NAME, LAST_NAME, AGE, SEX, EMPLOYEE_TITLE, DEPARTMENT, SALARY, TARGET, EMAIL, CITY, ADDRESS, MANAGER_ID) AS
(
Select 1, 'John', 'Doe', 35, 'M', 'TITLE A', '10', 2000, 2200, '[e-mail]', 'Boston', '79, Some Street', 9 From Dual Union All
Select 2, 'Sam', 'Smith', 25, 'M', 'TITLE C', '10', 1800, 2050, '[e-mail]', 'Boston', '321, Some Other Street', 9 From Dual Union All
Select 3, 'Jane', 'Doe', 31, 'F', 'TITLE B', '20', 1920, 2200, '[e-mail]', 'Boston', '79, Some Street', 8 From Dual Union All
Select 4, 'Ann', 'Chriss', 47, 'F', 'TITLE A', '20', 2100, 2500, '[e-mail]', 'Boston', '1110, Some Big Street', 8 From Dual Union All
Select 5, 'Bob', 'Flint', 54, 'M', 'TITLE A', '30', 2150, 2500, '[e-mail]', 'Boston', '1, Some Street', 7 From Dual 
),
sf_bonus (WORKER_REF_ID, BONUS) AS
(
Select 1, 175 From Dual Union All
Select 4, 200 From Dual Union All
Select 1, 145 From Dual Union All
Select 5, 250 From Dual 
)

可以在main Select中进行所有求和并计算平均值。根本不需要AVG()

主要sql:

SELECT    EMPLOYEE_TITLE, SEX, 
(Sum(e.SALARY) + Sum(Nvl(b.BONUS, 0))) / (Select count(*) From sf_employee Where EMPLOYEE_TITLE = e.EMPLOYEE_TITLE And SEX = e.SEX) "COMPENSATION"
FROM      sf_employee e
LEFT JOIN sf_bonus b ON(b.WORKER_REF_ID = e.ID)
WHERE     Nvl(b.BONUS, 0) <> 0
GROUP BY  EMPLOYEE_TITLE, SEX  

使用上述示例数据，结果为:

tbody> <<tr>标题

EMPLOYEE_TITLE	性别	COMPENSATION
标题一个	M	3360
	F	2300