Oracle 小计-汇总处理

假设我们有一个名为employees的表，它包含部门（department）、员工姓名（employee）和工资（salary）

CREATE TABLE employees (
    department VARCHAR2(50),
    employee VARCHAR2(50),
    salary NUMBER(10, 2)
);

初始化数据

INSERT INTO employees (department, employee, salary) VALUES ('Sales', 'John', 50000);
INSERT INTO employees (department, employee, salary) VALUES ('Sales', 'Jane', 70000);
INSERT INTO employees (department, employee, salary) VALUES ('Marketing', 'Lisa', 60000);
INSERT INTO employees (department, employee, salary) VALUES ('Marketing', 'Bob', 40000);

ROLLUP

SELECT department, employee, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, employee);

department | employee | total_salary
-----------+----------+-------------
| ---- | ---- | ---- |
Sales | John | 50000.00
Sales | Jane | 70000.00
Sales | NULL | 120000.00 (Sales部门的总和)
Marketing | Lisa | 60000.00
Marketing | Bob | 40000.00
Marketing | NULL | 100000.00 (Marketing部门的总和)
NULL | NULL | 220000.00 (所有部门的总和)