如果数据库有表A 字段A,B,C 数据为
A B C
a aa 10
a aa 20
a aaa 30
b aaa 40
b aaaa 50
b aaaa 60
要统计为:
输出形式为:
A B C
a aa 10
a aa 20
小计: 30 a aaa 30
b aaa 40
小计: 70 b aaaa 50
b aaaa 60
小计: 110
A B C
a aa 10
a aa 20
a aaa 30
b aaa 40
b aaaa 50
b aaaa 60
要统计为:
输出形式为:
A B C
a aa 10
a aa 20
小计: 30 a aaa 30
b aaa 40
小计: 70 b aaaa 50
b aaaa 60
小计: 110
DECODE(GROUPING(A),1,'小计',B),
SUM(C)
FROM A
GROUP BY ROLLUP(A,B);
DROP TABLE test;
CREATE TABLE test(a VARCHAR2(10),b VARCHAR2(10),c INT);
INSERT INTO TEST VALUES ('a', 'aa', 10);
INSERT INTO TEST VALUES ('a', 'aa', 20);
INSERT INTO TEST VALUES ('a', 'aaa', 30);
INSERT INTO TEST VALUES ('b', 'aaa', 40);
INSERT INTO TEST VALUES ('b', 'aaaa', 50);
INSERT INTO TEST VALUES ('b', 'aaaa', 60);
SELECT A, DECODE(GROUPING(A), 1, '小计:', B) B, SUM(C)
FROM TEST
GROUP BY ROLLUP(B, (A, C))
HAVING GROUPING_ID(B,a) <> 3;
输出:
a aa 10
a aa 20
小计: 30
a aaa 30
b aaa 40
小计: 70
b aaaa 50
b aaaa 60
小计: 110
from (select a,b,c,a as id
from a
union all
select '小计','',sum(c),a
from a
)
order by id,a
CREATE TABLE test(a VARCHAR2(10),b VARCHAR2(10),c INT);
INSERT INTO TEST VALUES ('a', 'aa', 10);
INSERT INTO TEST VALUES ('a', 'aa', 20);
INSERT INTO TEST VALUES ('a', 'aaa', 30);
INSERT INTO TEST VALUES ('b', 'aaa', 40);
INSERT INTO TEST VALUES ('b', 'aaaa', 50);
INSERT INTO TEST VALUES ('b', 'aaaa', 60);select a,b,c
from (select a,b,c,a as id
from test
union all
select '小计','',sum(c),b
from test group by b
)
order by B结果:
1 a aa 10
2 a aa 20
3 a aaa 30
4 b aaa 40
5 b aaaa 50
6 b aaaa 60
7 小计 30
8 小计 70
9 小计 110
这些都执行不了的呀!