create table DEPT
(
DEPT_ID VARCHAR2(10),
DEPT_NAME VARCHAR2(20),
ID VARCHAR2(10),
NAME VARCHAR2(20),
MONEY FLOAT
)我在oracle里安部门小计,然后再总计怎么写?
(
DEPT_ID VARCHAR2(10),
DEPT_NAME VARCHAR2(20),
ID VARCHAR2(10),
NAME VARCHAR2(20),
MONEY FLOAT
)我在oracle里安部门小计,然后再总计怎么写?
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('02','研发部','02','关羽',18500)
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('02','研发部','03','张飞',7000)
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','04','赵云',0)
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','05','黄忠',0)
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','06','马超',0)
GROUP BY ROLLUP(DEPT_ID)
WHEN GROUPING(t.a) = 1 THEN
'总计'
WHEN GROUPING(t.a) = 0 AND GROUPING(t.ida) = 1 THEN
T.A||'小计'
ELSE
T.A
END A,
t.ida,
SUM(t.d)
FROM table1 t
where t.a is not null
GROUP BY ROLLUP(t.a, t.ida);
你拿去改一下就可以用了
CASE grouping_id(t.dept_name, t.name)
WHEN 1 THEN
'小计'
WHEN 3 THEN
'合计'
ELSE
t.dept_name
END dept_name,
t.id,
t.name,
SUM(t.money) money
FROM dept1 t
GROUP BY ROLLUP((t.dept_id, t.dept_name), (t.id, t.name));
2 a 2 4
3 a 3 6
4 a小计 12
5 b 4 8
6 b 5 10
7 b小计 18
8 c 6 12
9 c 7 14
10 c 8 16
11 c小计 42
12 d 12 24
13 d 13 26
14 d小计 50
15 e 15 30
16 e小计 30
17 f 9 18
18 f 10 20
19 f 11 22
20 f 14 28
21 f小计 88
22 g 17 34
23 g 18 36
24 g 19 38
25 g小计 108
26 q 16 32
27 q小计 32
28 总计 380
(
DEPT_ID VARCHAR2(10),
DEPT_NAME VARCHAR2(20),
ID VARCHAR2(10),
NAME VARCHAR2(20),
MONEY FLOAT
);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('01','商务部','01','刘备',36800);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('02','研发部','02','关羽',18500);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('02','研发部','03','张飞',7000);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','04','赵云',0);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','05','黄忠',0);
insert into DEPT (DEPT_ID,DEPT_NAME,ID,NAME,MONEY) values('03','实施部','06','马超',0);
commit;select decode(grouping_id(DEPT_NAME), 0,DEPT_NAME, '总计'), sum(MONEY)
from dept
group by rollup(DEPT_NAME);
drop table dept;
T.NAME,
DECODE(GROUPING_ID(DEPT_ID, ID), 1, '小计', '3', '合计', T.DEPT_ID) DEPT_ID,
T.DEPT_NAME,
SUM(T.MONEY) SUMINFO
FROM DEPT T
GROUP BY ROLLUP((T.DEPT_ID, T.DEPT_NAME), (T.ID, T.NAME));执行如下:
SQL> SELECT T.ID,
2 T.NAME,
3 DECODE(GROUPING_ID(DEPT_ID, ID), 1, '小计', '3', '合计', T.DEPT_ID) DEPT_ID,
4 T.DEPT_NAME,
5 SUM(T.MONEY) SUMINFO
6 FROM DEPT T
7 GROUP BY ROLLUP((T.DEPT_ID, T.DEPT_NAME), (T.ID, T.NAME));ID NAME DEPT_ID DEPT_NAME SUMINFO
---------- -------------------- ---------- -------------------- ----------
01 刘备 01 商务部 36800
小计 商务部 36800
02 关羽 02 研发部 18500
03 张飞 02 研发部 7000
小计 研发部 25500
04 赵云 03 实施部 0
05 黄忠 03 实施部 0
06 马超 03 实施部 0
小计 实施部 0
合计 62300
oracle QQ群:54775466
欢迎爱好者 一起入群探讨