Table1
STA A B C D E F G H I J K L
2 0 3 0 44 0 15 0 0 1 0 0 0
4 0 4 0 648 0 4 2 22 0 6 0 0
6 0 0 0 1 0 270 0 0 0 0 0 0
7 0 1 0 6 23 4100 0 0 0 3 0 1
9 0 0 0 0 0 16 0 0 0 0 0 0Table2
STA A B C D E F G H I J K L 合计
毕业 0 3 0 45 0 285 0 0 1 0 0 0 334
升级 0 5 0 654 23 4104 2 22 0 9 0 0 4819
留级 0 0 0 0 0 16 0 0 0 0 0 0 16Table2解释:
“毕业”是table1中sta为2,6的和,“升级”是table1中的sta为4和7的和,“留级”table1中的9那一行值,合计为每一行的合计请问怎样通过从table1得到table2的结果??
STA A B C D E F G H I J K L
2 0 3 0 44 0 15 0 0 1 0 0 0
4 0 4 0 648 0 4 2 22 0 6 0 0
6 0 0 0 1 0 270 0 0 0 0 0 0
7 0 1 0 6 23 4100 0 0 0 3 0 1
9 0 0 0 0 0 16 0 0 0 0 0 0Table2
STA A B C D E F G H I J K L 合计
毕业 0 3 0 45 0 285 0 0 1 0 0 0 334
升级 0 5 0 654 23 4104 2 22 0 9 0 0 4819
留级 0 0 0 0 0 16 0 0 0 0 0 0 16Table2解释:
“毕业”是table1中sta为2,6的和,“升级”是table1中的sta为4和7的和,“留级”table1中的9那一行值,合计为每一行的合计请问怎样通过从table1得到table2的结果??
sum(aa.b) b,
.
.
.
.
sum(aa.l) l
from
(
select decode(sta,2,'毕业',6,'毕业',4,'升级',7,'升级','留级') sta,
a,b,c,d,e,f,g,h,i,j,k,l
from table 1)
group by aa.sta
就是sum(aa.a)+sum(aa.b)+...+sum(aa.l)
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 2 and sta = 6
union
select '升级' as sta sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 4 and sta =7 union
select '留级' as sta sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 9
试试
你这个说的是最后用程序控制和的结果吧,能不能都写在sql中 通过sql语句 一起得到
select '毕业' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 2 or sta = 6
union
select '升级' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 4 or sta =7 union
select '留级' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l from table1 where sta = 9
select '毕业' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l, sum(a)+...+sum(l) as 合计 from table1 where sta = 2 or sta = 6
union
select '升级' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l, sum(a)+...+sum(l) as 合计 from table1 where sta = 4 or sta =7 union
select '留级' as sta, sum(A) A, sum(B) B, sum(c) c, sum(d) d, sum(e) e, sum(f) f ,
sum(g)g, sum(h) h, sum(i) i, sum(j) j, sum(k) k, sum(l) l, sum(a)+...+sum(l) as 合计 from table1 where sta = 9