需要一种方法来添加从MySQL查询中获得的结果

Question

我有以下表格

• Employee(eid, ename, deptid)
• ESal(eid, salary)
• EDept(deptid, dept)

是否有一种方法可以获得部门支付的总薪资，也可以在同一查询中获得支付的总薪资。

就像这样：

   Dept1  |  100 
   Dept2  |  300
   Dept3  |  200
   Total  |  600

Answer 1

我做了以下工作来解决相同的问题

实际上，我使用left join和esal表执行了一个employee表，并按工资执行了一个组。为了得到执行总额的WITH ROLLUP，它给出了所有检索到的薪水的总数

mysql> select * from employee;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
|   1 | emp1  |      1 |
|   2 | emp2  |      1 |
|   3 | emp3  |      2 |
|   4 | emp4  |      3 |
|   5 | emp5  |      3 |
|   6 | emp6  |      2 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> select * from edept;
+--------+-------+
| deptid | dept  |
+--------+-------+
|      1 | HR    |
|      2 | Admin |
|      3 | Dev   |
+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from esal;
+-----+--------+
| eid | salary |
+-----+--------+
|   1 |    100 |
|   2 |    200 |
|   3 |    230 |
|   4 |    120 |
|   5 |    400 |
|   6 |    100 |
+-----+--------+
6 rows in set (0.00 sec)


   SELECT ifnull(dept, 'Total') as 'Dept', sum( salary ) 
       AS 'salary' FROM employee 
LEFT JOIN edept  ON employee.deptid = edept.deptid 
LEFT JOIN esal  ON employee.eid = esal.eid 
 GROUP BY dept 
     with rollup;

+-------+--------+
| Dept  | salary |
+-------+--------+
| Admin |    330 |
| Dev   |    520 |
| HR    |    300 |
| Total |   1150 |
+-------+--------+
4 rows in set, 1 warning (0.00 sec)

Answer 2

您可以尝试使用友联市。可能是这样的：

(
SELECT
    EDept.dept,
    e.salary
FROM
    EDept 
    JOIN (
        SELECT
            e.deptid,
            SUM(s.salary) as salary
        FROM
            Employee as e
            JOIN ESal as s
                ON e.eid = s.eid
        GROUP BY 
            e.deptid
    ) as e
        ON e.deptid = EDept.deptid
)
UNION
(
SELECT
    "Total",
    SUM(salary)
FROM
    ESal
)