select decode(grouping(ND),1,'市场合计', CENTER) CENTER, decode(grouping(ND),1,'', BIGCLASS) BIGCLASS, decode(grouping(ND),1,'',ND) ND, sum(MONEY) MONEY from TABNAME1 group by rollup(CENTER,BIGCLASS,ND);
Examples In the following example, which uses the sample tables hr.departments and hr.employees, if the GROUPING function returns 1 (indicating a superaggregate row rather than a regular row from the table), then the string "All Jobs" appears in the "JOB" column instead of the null that would otherwise appear:SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY ROLLUP (department_name, job_id);DEPARTMENT JOB Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 Administration All Jobs 1 52800 Executive AD_PRES 1 288000 Executive AD_VP 2 204000 Executive All Jobs 3 232000 Finance FI_ACCOUNT 5 95040 Finance FI_MGR 1 144000 Finance All Jobs 6 103200 . . .
Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id);DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . All Departments ST_MAN 5 87360 All Departments All Jobs 107 77798.1308
上面的重复记录去掉就差不多了…… 比如: 400 北京 市场 200 北京 市场 a 600 -_-# 500 天津 新市场 300 天津 新市场 d 100 上海 新市场 e 900 -_-# 1500 total 其实我实际想要的是: 400 北京 市场 200 北京 市场 a 600 市场小计 500 天津 新市场 300 天津 新市场 d 100 上海 新市场 e 900 新市场小计 1500 total
因为实际上那个bigclass我是不打算显示的~
SQL> Select Bigclass, Center, Nd, Money, Memo 2 From T1 3 Union 4 Select Bigclass Bigclass, bigclass||'小计' As Center, '' Nd, Sum(Money) Money, '' Memo 5 From T1 6 Group By Bigclass 7 Union 8 Select '' As Bigclass , '合计' As Center, '' Nd, Sum(Money) Money, '' Memo 9 From T1 10 Order By Bigclass 11 ;BIGCLASS CENTER ND MONEY MEMO -------------------- ------------------------ ---------- ---------- ---------- 市场 北京 2004-1 200 市场 北京 2004-2 400 市场 市场小计 600 新市场 上海 2004-1 100 新市场 天津 2004-1 300 新市场 天津 2004-2 500 新市场 新市场小计 900 合计 1500 8 行 已选择SQL>
楼上朋友,你现在的结果看起来是正确的,可是有个问题…… 比如:当我将“北京”换成“西藏”,你看结果……SELECT Bigclass, Center, Nd, Money, Memo FROM T1 UNION SELECT Bigclass Bigclass, bigclass||'小计' AS Center, '' Nd, SUM(Money) Money, '' Memo FROM T1 GROUP BY Bigclass UNION SELECT '' AS Bigclass , '合计' AS Center, '' Nd, SUM(Money) Money, '' Memo FROM T1 ORDER BY BigclassBIGCLASS CENTER ND MONEY MEMO -------------------- ------------------------- -------------------- ---------- ----- 市场 市场小计 600 市场 西藏 2004-1 200 a 市场 西藏 2004-2 400 新市场 上海 2004-1 100 e 新市场 天津 2004-1 300 d 新市场 天津 2004-2 500 新市场 新市场小计 900 合计 1500 8 rows selected
因为那个例子的结果虽然好像符合要求,可在我的表里是不是那种结果的,
那个例子里的是类似于:
北京
北京
北京合计
这样的,
我要求的不是啊~我的要求是用来分组的那个字段的内容平时不显示,只是在合计的时候显示下。这个只是个例子,实际上我那个结果的要求是,
BIGCLASS 那个字段实际是不显示的~因为他是个数字,
“新市场合计”字样里面的东西是从另一个表关联取来的。所以有上面提到的要求……
decode(grouping(ND),1,'', BIGCLASS) BIGCLASS,
decode(grouping(ND),1,'',ND) ND,
sum(MONEY) MONEY
from TABNAME1
group by rollup(CENTER,BIGCLASS,ND);
你的运行结果:
SELECT DECODE(grouping(ND),1,'市场合计', CENTER) CENTER,
DECODE(grouping(ND),1,'', BIGCLASS) BIGCLASS,
DECODE(grouping(ND),1,'',ND) ND,
SUM(MONEY) MONEY
FROM T1
GROUP BY rollup(CENTER,BIGCLASS,ND)
CENTER BIGCLASS ND MONEY
------------------------- -------------------- -------------------- ----------
北京 市场 2004-1 600
北京 市场 2004-2 1200
市场合计 1800
市场合计 1800
上海 新市场 2004-1 300
市场合计 300
市场合计 300
天津 新市场 2004-1 900
天津 新市场 2004-2 1500
市场合计 2400
市场合计 2400
市场合计 4500
12 rows selected
晕,呵呵~之所以在帖里没用decode+grouping方法,
1。开始不怎么会用,用了之后发现结果不太符合,不知道如何处理。
2。原来为了在客户端实现好看点的报表,打算自己算合计……所以找其他方法看能否去掉合计试试现在我知道现在这种方法如何实现我的要求了,正在测试如何用大家说的decode+grouping+rollup实现……
06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:37 2 from t
06:53:38 3 group by rollup(area_code,bill_month)
06:53:49 4 /AREA_CODE BILL_MONTH LOCAL_FARE
---------- --------------- --------------
5761 200405 13060433.89
5761 200406 13318931.01
5761 200407 13710265.93
5761 200408 14135782.21
5761 54225413.04
5762 200405 12643792.11
5762 200406 12795060.65
5762 200407 13224298.12
5762 200408 13376468.72
5762 52039619.60
5763 200405 16649778.91
5763 200406 17120515.71
5763 200407 17487493.32
5763 200408 17928757.08
5763 69186545.02
5764 200405 12487791.94
5764 200406 13295187.67
5764 200407 13444093.76
5764 200408 13929695.09
5764 53156768.46
5765 200405 25057737.47
5765 200406 26058461.31
5765 200407 26301881.40
5765 200408 27130639.01
5765 104548719.19
333157065.31
A
B
C
小计
。。
小计
合计 。这如何实现呢?
In the following example, which uses the sample tables hr.departments and hr.employees, if the GROUPING function returns 1 (indicating a superaggregate row rather than a regular row from the table), then the string "All Jobs" appears in the "JOB" column instead of the null that would otherwise appear:SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
department_name) AS department,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);DEPARTMENT JOB Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144000
Accounting All Jobs 2 121800
Administration AD_ASST 1 52800
Administration All Jobs 1 52800
Executive AD_PRES 1 288000
Executive AD_VP 2 204000
Executive All Jobs 3 232000
Finance FI_ACCOUNT 5 95040
Finance FI_MGR 1 144000
Finance All Jobs 6 103200
.
.
.
To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables hr.employees and hr.departments:SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
department_name) AS department_name,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY CUBE (department_name, job_id);DEPARTMENT_NAME JOB_ID Total Empl Average Sal
------------------------------ ---------- ---------- -----------
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144000
Accounting All Jobs 2 121800
Administration AD_ASST 1 52800
.
.
.
All Departments ST_MAN 5 87360
All Departments All Jobs 107 77798.1308
我的表有个多数都是null的字段“memo”,如果我在汇总sql加入这个字段,
那么结果中的除了汇总数据之外的普通数据都变成双份的了……晕啊~
请教各位,这怎么搞定呢?
SQL> Insert Into t1 Values('市场','北京', '2004-1',200,'');1 行 已插入SQL> Insert Into t1 Values('市场','北京', '2004-2',400,'');1 行 已插入SQL> Insert Into t1 Values('新市场','天津', '2004-1',300,'');1 行 已插入SQL> Insert Into t1 Values('新市场','天津', '2004-2',500,'');1 行 已插入SQL> Insert Into t1 Values('新市场','上海', '2004-1',100,'');1 行 已插入SQL>
SQL> Select bigclass, center, nd,money ,memo From t1
2 Union
3 Select bigclass||'合计', '' As center, '' nd,Sum(money)*2,'' memo From t1 Group By bigclass
4 ;BIGCLASS CENTER ND MONEY MEMO
------------------------ -------------------- ---------- ---------- ----------
市场 北京 2004-1 200
市场 北京 2004-2 400
市场合计 1200
新市场 上海 2004-1 100
新市场 天津 2004-1 300
新市场 天津 2004-2 500
新市场合计 1800 7 行 已选择SQL>
我在sum()时就乘以二了.
SELECT SUM(money), center,
DECODE(grouping(bigclass), 1, 'total', DECODE(grouping(memo), 1, '-_-#', bigclass)) bigclass, memo FROM T1
GROUP BY rollup(bigclass, memo, center)
SUM(MONEY) CENTER BIGCLASS MEMO
---------- ------------------------- -------------------- -----
400 北京 市场
400 市场
200 北京 市场 a
200 市场 a
600 -_-#
500 天津 新市场
500 新市场
300 天津 新市场 d
300 新市场 d
100 上海 新市场 e
100 新市场 e
900 -_-#
1500 total
13 rows selected
SELECT * FROM T1
BIGCLASS CENTER ND MONEY MEMO
-------------------- ------------------------- -------------------- ------ -----
市场 北京 2004-1 200 a
市场 北京 2004-2 400
新市场 天津 2004-1 300 d
新市场 天津 2004-2 500
新市场 上海 2004-1 100 e
5 rows selected
我想要的是根据bigclass汇总,但“合计”“小计”字样要写在center处。
可现在的结果却是……大家看到了,都成对了,应该是我写错了,可怎么处理呢?
比如:
400 北京 市场
200 北京 市场 a
600 -_-#
500 天津 新市场
300 天津 新市场 d
100 上海 新市场 e
900 -_-#
1500 total 其实我实际想要的是:
400 北京 市场
200 北京 市场 a
600 市场小计
500 天津 新市场
300 天津 新市场 d
100 上海 新市场 e
900 新市场小计
1500 total
2 From T1
3 Union
4 Select Bigclass Bigclass, bigclass||'小计' As Center, '' Nd, Sum(Money) Money, '' Memo
5 From T1
6 Group By Bigclass
7 Union
8 Select '' As Bigclass , '合计' As Center, '' Nd, Sum(Money) Money, '' Memo
9 From T1
10 Order By Bigclass
11 ;BIGCLASS CENTER ND MONEY MEMO
-------------------- ------------------------ ---------- ---------- ----------
市场 北京 2004-1 200
市场 北京 2004-2 400
市场 市场小计 600
新市场 上海 2004-1 100
新市场 天津 2004-1 300
新市场 天津 2004-2 500
新市场 新市场小计 900
合计 1500 8 行 已选择SQL>
比如:当我将“北京”换成“西藏”,你看结果……SELECT Bigclass, Center, Nd, Money, Memo
FROM T1
UNION
SELECT Bigclass Bigclass, bigclass||'小计' AS Center, '' Nd, SUM(Money) Money, '' Memo
FROM T1
GROUP BY Bigclass
UNION
SELECT '' AS Bigclass , '合计' AS Center, '' Nd, SUM(Money) Money, '' Memo
FROM T1
ORDER BY BigclassBIGCLASS CENTER ND MONEY MEMO
-------------------- ------------------------- -------------------- ---------- -----
市场 市场小计 600
市场 西藏 2004-1 200 a
市场 西藏 2004-2 400
新市场 上海 2004-1 100 e
新市场 天津 2004-1 300 d
新市场 天津 2004-2 500
新市场 新市场小计 900
合计 1500
8 rows selected
----------------
都反了吧?:(
是解决这个问题(显示上述结果)的方法,并非是他们组合的用法~
sum on money