求一SQL
SELECT
field1,
field2,
sum(field3+field4) as a,
sum(field5+field6) as b
FROM table
这句SQL会得到两条记录，r如下：
field1 field2 a b
AA BB 12 22
CC DD 13 23
现在我想再得到一条记录
DD DD 25 45
就是把上两条记录相加
谢谢
问题点数：50、回复次数：1Top
1 楼duanzilin（寻）回复于 2006-06-01 13:11:39 得分 50
参考下面的例子：
SQL> select job,ename,sal,bonus from emp;
JOB ENAME SAL BONUS
--------- ---------- --------- ----------
CLERK SMITH 800.00 200
SALESMAN ALLEN 1600.00 300
SALESMAN WARD 1250.00 100
MANAGER JONES 2975.00 500
SALESMAN MARTIN 1250.00 200
MANAGER BLAKE 2850.00 100
MANAGER CLARK 2450.00 200
ANALYST SCOTT 3000.00 100
PRESIDENT KING 5000.00 100
SALESMAN TURNER 1500.00 200
CLERK ADAMS 1100.00 300
CLERK JAMES 950.00 300
ANALYST FORD 3000.00 300
CLERK MILLER 1300.00 300
14 rows selected
SQL>
SQL> SELECT decode(grouping_id(job, ename), 0, ename, 1, job , '合计') job,
2 decode(grouping_id(job, ename), 0, job, 1, '小计', '合计') ename,
3 SUM(sal + bonus) earn
4 FROM emp t
5 GROUP BY ROLLUP(job, ename)
6 /
JOB ENAME EARN
---------- --------- ----------
ADAMS CLERK 1400
JAMES CLERK 1250
SMITH CLERK 1000
MILLER CLERK 1600
CLERK 小计 5250
FORD ANALYST 3300
SCOTT ANALYST 3100
ANALYST 小计 6400
BLAKE MANAGER 2950
CLARK MANAGER 2650
JONES MANAGER 3475
MANAGER 小计 9075
WARD SALESMAN 1350
ALLEN SALESMAN 1900
MARTIN SALESMAN 1450
TURNER SALESMAN 1700
SALESMAN 小计 6400
KING PRESIDENT 5100
PRESIDENT 小计 5100
合计 合计 32225
20 rows selected
下面去掉小计：
SQL>
SQL> SELECT decode(grouping_id(job, ename), 0, ename, 1, job , '合计') job,
2 decode(grouping_id(job, ename), 0, job, 1, '小计', '合计') ename,
3 SUM(sal + bonus) earn
4 FROM emp t
5 GROUP BY ROLLUP(job, ename)
6 HAVING GROUPING_id(job, ename) IN (0, 3)
7 /
JOB ENAME EARN
---------- --------- ----------
ADAMS CLERK 1400
JAMES CLERK 1250
SMITH CLERK 1000
MILLER CLERK 1600
FORD ANALYST 3300
SCOTT ANALYST 3100
BLAKE MANAGER 2950
CLARK MANAGER 2650
JONES MANAGER 3475
WARD SALESMAN 1350
ALLEN SALESMAN 1900
MARTIN SALESMAN 1450
TURNER SALESMAN 1700
KING PRESIDENT 5100
合计 合计 32225
15 rows selectedTop
