在sql server中,汇总是用compute来写的
如:
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)
------------------------
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
但是在Oracle中,怎么汇总呀?
如:
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)
------------------------
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
但是在Oracle中,怎么汇总呀?
FROM titles
group by rollup(type)示例:
SQL> select deptno,empno,sal from emp;DEPTNO EMPNO SAL
------ ----- ---------
20 7369 800.00
30 7499 1600.00
30 7521 1250.00
20 7566 2975.00
30 7654 1250.00
30 7698 2850.00
10 7782 2450.00
20 7788 3000.00
10 7839 5000.00
30 7844 1500.00
20 7876 1100.00
30 7900 950.00
20 7902 3000.00
10 7934 1300.0014 rows selectedSQL> SELECT decode(GROUPING(empno), 1, '合计', empno) empno, SUM(sal) sal FROM emp t GROUP BY ROLLUP(empno);EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
合计 2902515 rows selectedSQL>
SQL> SELECT decode(grouping_id(deptno, empno), 3, '合计', deptno) deptno,
2 decode(grouping_id(deptno, empno), 1, '小计', empno) empno,
3 SUM(sal) sal
4 FROM emp t
5 GROUP BY ROLLUP(deptno, empno)
6 /DEPTNO EMPNO SAL
---------- ---------- ----------
10 7782 2450
10 7839 5000
10 7934 1300
10 小计 8750
20 7369 800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
20 小计 10875
30 7900 950
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
30 小计 9400
合计 2902518 rows selected
谢谢!!!
三个group函数: grouping(), grouping_id, group_id
设置混合列: grouping sets基本上自学要全记住很难啦,因为不常, 建议看一下, 等用到时再翻资料查