mysql - 如何在 MYSQL 中的优化/一个查询中按组进行嵌套求和？

Question

我有 3 个表，Manager、Employee 和 Salary。以下是所有表的结构。

经理

id  | Name
---------
111 | AAA
222 | BBB

员工

id  | Name | Manager_id | new_policy_deductions
----------------------------------
1   | A B  | 111        | 100
2   | A C  | 111        | 200
3   | C D  | 222        | 200

工资

id  | employee_id | Month | Emp_Salary | Manager_id
---------------------------------------------------
1   | 1           | Jan   | 500        | 111
2   | 1           | Feb   | 500        | 111
3   | 1           | Mar   | 600        | 111
4   | 2           | Apr   | 500        | 111
5   | 1           | Apr   | 700        | 111
6   | 3           | Mar   | 300        | 222
7   | 3           | Apr   | 500        | 222

employee_id 是 Employee 表到 Salary 表的外键 & manager_id 是 的外键Manager 表到其他表。

现在，我需要构造一个查询，以便获得以下结果。

Manager_id | Net_Salary
-----------------------
111        | 2500
222        | 600

我是如何达到这些数字的？

将一名经理手下所有员工的薪水总和 (500 + 500 + 600 + 500 + 700 = 2800)，然后减去该经理的所有新政策扣除额 (100 + 200 = 300)。这意味着 111 将有 2500 (2800 - 300)。同样，对于 222，我们有 600。

我使用 2 个查询实现了这一点，如下所示，

x = select manager_id, sum(Emp_Salary) from Salary group by manager_id
y = select manager_id, sum(new_policy_deductions) from Employee group by manager_id

result = x - y

这可以在单个 SQL 查询中实现吗？如果是，如何？

注意:

• 实际的表名与我在这里使用的不同。
• 我无法修改表结构。它是很久以前设计的。
• 不允许嵌套SQL查询，因为那相当于2次查询，效率很低。

编辑:

以下是查询，这将有助于创建虚拟数据。

create table manager (id int, name text);

create table employee (id int, name text, manager_id int, new_policy_deductions int);

create table salary(id int, employee_id int, emp_salary int, manager_id int);

select * from manager;
INSERT INTO manager
(`id`,
`name`)
VALUES
(111,'AAA'), (222,'BBB');

select * from employee;

INSERT INTO employee
(`id`,
`name`,
`manager_id`,
`new_policy_deductions`)
VALUES
(1,'A B',111,100), (2,'A C B',111,200), (3,'C A B',222,200);


select * from salary;
INSERT INTO salary
(`id`,
`employee_id`,
`month`,
`emp_salary`,
`manager_id`)
VALUES
(1,1,'Jan',500,111), (2,1,'Feb',500,111), (3,1,'Mar',600,111), (4,2,'Apr',500,111), (5,1,'Apr',700,111), (6,3,'Mar',300,222), (7,3,'Apr',500,222);

我忽略了查询中的外键约束，因为它是虚拟数据。实际表确实有外键约束。

Answer 1

试试这个:

SELECT t1.Manager_id, sumOfSalaries - sumOfDeductions 
FROM (
  SELECT Manager_id, SUM(Emp_Salary) AS sumOfSalaries
  FROM Salary 
  GROUP BY Manager_id) AS t1
INNER JOIN (
  SELECT Manager_id, SUM(new_policy_deductions) AS sumOfDeductions
  FROM Employee
  GROUP BY Manager_id
) AS t2 ON t1.Manager_id = t2.Manager_id

编辑:

SELECT t1.Id, t1.Name, 
       COALESCE(sumOfSalaries, 0) - COALESCE(sumOfDeductions, 0) AS Net_Salary
FROM Manager AS t1
LEFT JOIN (
  SELECT Manager_id, SUM(Emp_Salary) AS sumOfSalaries
      FROM Salary 
      GROUP BY Manager_id
) AS t2 ON t1.Id = t2.Manager_id
  INNER JOIN (
      SELECT Manager_id, SUM(new_policy_deductions) AS sumOfDeductions
      FROM Employee
      GROUP BY Manager_id
) AS t3 ON t2.Manager_id = t3.Manager_id