-- Start
不知道大家听没听说过小计,总计等会计词语,如果你做过报表,一定不会陌生。
CREATE TABLE employee
(
name NVARCHAR2(10),
gender NCHAR(1),
country NVARCHAR2(10),
department NVARCHAR2(10),
salary NUMBER(10)
);
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);
通常我们是在生成报表时算小计或总计的,其实通过 SQL 就可以实现。
SELECT
country,
department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY ROLLUP(country, department);
country department AVG
中国 市场部 4500
中国 技术部 3500
中国 null 4000
美国 市场部 3000
美国 技术部 4000
美国 null 3500
null null 3833.33
上面的 null 是不是让人看着很不爽,要是能返回有意义的值就更好了,为此 Oralce 提供几个函数实现这个功能。
SELECT
DECODE(GROUPING(country), 1, '总计', country) AS country,
DECODE(GROUPING(department), 1, '小计', department) AS department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY ROLLUP(country, department);
country department AVG
中国 市场部 4500
中国 技术部 3500
中国 小计 4000
美国 市场部 3000
美国 技术部 4000
美国 小计 3500
总计 小计 3833.33
还有个 CUBE 关键字,它比 ROLLUP 语句返回更多的内容,以下是将上面语句的 ROLLUP 替换为 CUBE 后得到的结果:
SELECT
DECODE(GROUPING(country), 1, '总计', country) AS country,
DECODE(GROUPING(department), 1, '小计', department) AS department,
round(avg(salary), 2) AVG
FROM
employee
GROUP BY CUBE(country, department);
country department AVG
总计 小计 3833.33
总计 市场部 4000
总计 技术部 3666.67
中国 小计 4000
中国 市场部 4500
中国 技术部 3500
美国 小计 3500
美国 市场部 3000
美国 技术部 4000
--更多参见:Oracle SQL 精萃
-- 声明:转载请注明出处
-- Last edited on 2015-02-28
-- Created by ShangBo on 2015-02-28
-- End