楼上说的一是一个权益之计,上面的只是个特例,比如我现在又要限定合计的时候按 aa =10 bb =15来合计怎么办?上面的方法就行不通了:)同样要加在后面
select aa , bb ,cc,from A where aa =&1 union select sum(aa),sum(bb),sum(cc) from A where aa =&1
四楼的可能还是我没有描述清楚,比如 select sum(aa),sum(bb),sum(cc) from (select aa , bb ,cc,from A where aa =10) Tab Tab.bb =10 其实我们已经把select aa , bb ,cc,from A where aa =10的结果定义为一个Tab了,可是这个Tab只在这个查询里有效,有没有一种方法使他在全局都有效,有点类似与变量的概念,比如我下次再用的时候就是 select sum(aa),sum(bb),sum(cc) from Tab where Tab.bb =15 Tab就为上次查询的select aa , bb ,cc,from A where aa =10
楼上的可能还没有看懂我的意思,如果我不单纯的对aa=11的进行汇总,我还要对BB=123,aa=11的也汇总,并且结果也加在后面怎么办? 这种情况sql语句就是 select aa , bb ,cc,from A where aa =10 union select sum(aa),sum(bb),sum(cc) from A where aa =10 union select sum(aa),sum(bb),sum(cc) from A where bb=123,aa=10 说来说去就是没有表变量的问题就是select sum(aa),sum(bb),sum(cc) from A where aa =10结果没地方保存每次用的时候 都要重新查询.除非建临时表,如果有表变量就好啦,我记得SQLSERVER有的就象 yedong309(遨游) 说的那样
MsSQL 就有这个功能(group by +compute),ORACLE 不用问也有。
那你看这样行不行: SQL> select rownum,aa,bb ,sum(aa) saa,sum(bb) sbb, sum(cc) scc from A 2 where aa = 11 3 group by cube(rownum,aa,bb) 4 having grouping_id(rownum,aa,bb) in (0,4,7) 5 order by rownum,aa,bb 6 / ROWNUM AA BB SAA SBB SCC ---------- ---------- ---------- ---------- ---------- ---------- 1 11 2354 11 2354 778 2 11 3 11 3 3 3 11 123 11 123 122 4 11 232 11 232 1232 5 11 232 11 232 1232 6 11 123 11 123 122 7 11 3 11 3 3 8 11 123 11 123 122 9 11 3 11 3 3 10 11 232 11 232 1232 11 11 123 11 123 122 12 11 123 11 123 122 13 11 123 11 123 122 14 11 123 11 123 122 15 11 232 11 232 1232 11 3 33 9 9//bb为3的合计 11 123 77 861 854//bb为123的合计 11 232 44 928 4928 11 2354 11 2354 778 165 4152 6569//所有合计其实这种问题可以先求出所有种类合计,如上面的,然后再在外面套一层循环,加上所有条件就可以了 select * from ( select rownum,aa,bb ,sum(aa) saa,sum(bb) sbb, sum(cc) scc from A group by cube(rownum,aa,bb) having grouping_id(rownum,aa,bb) in (0,4,7) order by rownum,aa,bb ) where aa = 11 and bb = 123如果还要统计cc的值,只要cube(rownum,aa,bb,cc)就可以了
楼主的那句select aa , bb ,cc,from A where aa =10 union select sum(aa),sum(bb),sum(cc) from A where aa =10 union select sum(aa),sum(bb),sum(cc) from A where bb=123,aa=10和select aa , bb ,cc,from A where aa =10 union select sum(aa),sum(bb),sum(cc) from A where aa =10最后出来的结果有什么差别吗?
union
select sum(aa),sum(bb),sum(cc) from A where aa =&1
select sum(aa),sum(bb),sum(cc)
from
(select aa , bb ,cc,from A where aa =10) Tab
Tab.bb =10
其实我们已经把select aa , bb ,cc,from A where aa =10的结果定义为一个Tab了,可是这个Tab只在这个查询里有效,有没有一种方法使他在全局都有效,有点类似与变量的概念,比如我下次再用的时候就是
select sum(aa),sum(bb),sum(cc)
from Tab
where Tab.bb =15
Tab就为上次查询的select aa , bb ,cc,from A where aa =10
select sum(aa),sum(bb),sum(cc) from A
where aa = 10
group by rollup(rownum)
2 where aa = 11
3 / aa bb cc
----------- --------------- -------------------
11 2354 778
11 3 3
11 123 122
11 232 1232
11 232 1232
11 123 122
11 3 3
11 123 122
11 3 3
11 232 1232
11 123 122
11 123 122
11 123 122
11 123 122
11 232 1232
你要的是这个结果吗,最后1行是汇总
SQL> select sum(aa),sum(bb),sum(cc) from A
2 where aa = 11
3 group by rollup(rownum)
4 /SUM(aa) SUM(bb) SUM(cc)
---------------- -------------------- ------------------------
11 2354 778
11 3 3
11 123 122
11 232 1232
11 232 1232
11 123 122
11 3 3
11 123 122
11 3 3
11 232 1232
11 123 122
11 123 122
11 123 122
11 123 122
11 232 1232
165 4152 6569
3 group by rollup(rownum)
4 / ROWNUM SUM(AA) SUM(BB) SUM(CC)
---------- ---------------- -------------------- ------------------------
1 11 2354 778
2 11 3 3
3 11 123 122
4 11 232 1232
5 11 232 1232
6 11 123 122
7 11 3 3
8 11 123 122
9 11 3 3
10 11 232 1232
11 11 123 122
12 11 123 122
13 11 123 122
14 11 123 122
15 11 232 1232
165 4152 6569
这样写应该明白了吧,rownum是伪列,相当于行号
这种情况sql语句就是
select aa , bb ,cc,from A where aa =10
union
select sum(aa),sum(bb),sum(cc) from A where aa =10
union
select sum(aa),sum(bb),sum(cc) from A where bb=123,aa=10
说来说去就是没有表变量的问题就是select sum(aa),sum(bb),sum(cc) from A where aa =10结果没地方保存每次用的时候 都要重新查询.除非建临时表,如果有表变量就好啦,我记得SQLSERVER有的就象 yedong309(遨游) 说的那样
SQL> select rownum,aa,bb ,sum(aa) saa,sum(bb) sbb,
sum(cc) scc from A
2 where aa = 11
3 group by cube(rownum,aa,bb)
4 having grouping_id(rownum,aa,bb) in (0,4,7)
5 order by rownum,aa,bb
6 / ROWNUM AA BB SAA SBB SCC
---------- ---------- ---------- ---------- ---------- ----------
1 11 2354 11 2354 778
2 11 3 11 3 3
3 11 123 11 123 122
4 11 232 11 232 1232
5 11 232 11 232 1232
6 11 123 11 123 122
7 11 3 11 3 3
8 11 123 11 123 122
9 11 3 11 3 3
10 11 232 11 232 1232
11 11 123 11 123 122
12 11 123 11 123 122
13 11 123 11 123 122
14 11 123 11 123 122
15 11 232 11 232 1232
11 3 33 9 9//bb为3的合计
11 123 77 861 854//bb为123的合计
11 232 44 928 4928
11 2354 11 2354 778
165 4152 6569//所有合计其实这种问题可以先求出所有种类合计,如上面的,然后再在外面套一层循环,加上所有条件就可以了
select * from (
select rownum,aa,bb ,sum(aa) saa,sum(bb) sbb,
sum(cc) scc from A
group by cube(rownum,aa,bb)
having grouping_id(rownum,aa,bb) in (0,4,7)
order by rownum,aa,bb
)
where aa = 11 and bb = 123如果还要统计cc的值,只要cube(rownum,aa,bb,cc)就可以了
sum(cc) scc from A
2 where aa in (9,11)
3 group by cube(rownum,aa,bb)
4 having grouping_id(rownum,aa,bb) in (0,4,5,7)
5 order by rownum,aa,bb
6 / ROWNUM AA BB SAA SBB SCC
---------- ---------- ---------- ---------- ---------- ----------
1 9 12 9 12 778
2 11 2354 11 2354 778
3 9 2354 9 2354 778
4 11 3 11 3 3
5 11 123 11 123 122
6 11 232 11 232 1232
7 11 232 11 232 1232
8 11 123 11 123 122
9 9 2354 9 2354 778
10 9 2354 9 2354 778
11 11 3 11 3 3
12 11 123 11 123 122
13 11 3 11 3 3
14 11 232 11 232 1232
15 11 123 11 123 122
16 11 123 11 123 122
17 11 123 11 123 122
18 11 123 11 123 122
19 11 232 11 232 1232
9 12 9 12 778
9 2354 27 7062 2334
9 36 7074 3112
11 3 33 9 9
11 123 77 861 854
11 232 44 928 4928
11 2354 11 2354 778
11 165 4152 6569
201 11226 9681
union
select sum(aa),sum(bb),sum(cc) from A where aa =10
union
select sum(aa),sum(bb),sum(cc) from A where bb=123,aa=10和select aa , bb ,cc,from A where aa =10
union
select sum(aa),sum(bb),sum(cc) from A where aa =10最后出来的结果有什么差别吗?