select sum(A.total),sum(B.total) from table A where A.code1='1' and A.code3 in('01','02','03','10','11') union all select sum(A.total),sum(B.total) from table A where A.code1='1' and B.code3 in('10','11');
好像不行啊,错误在第一行的sum(B.total)
select sum(A.total),sum(A.total) from table A where A.code1='1' and A.code3 in('01','02','03','10','11') union all select sum(A.total),sum(A.total) from table A where A.code1='1' and A.code3 in('10','11');
select sum(A.total),sum(B.total) from table A ,table B where A.code1='1' and A.code3 in('01','02','03','10','11') and a.code2=b.code2 union all select sum(A.total),sum(B.total) from table A ,table B where A.code1='1' and B.code3 in('10','11'); and a.code2=b.code2
select sum(A.total),sum(B.total) from table A,table B where A.code1='1' and B.code1='1' and A.code3 in('01','02','03','10','11') and B.code3 in('10','11') and A.code2=B.code2 group by code2;
select sum(decode(sign(code3-11),1,0,code4)), sum(decode(sign(10-code3),1,code4,0)) from tbname group by code2;
如果没有其它字符的话,没有问题,有其它字符的话如(a,b,c...) 就有问题了。
select (SELECT SUM(B.t) FROM TEST B WHERE B.C1 = '1' AND B.C2 = A.C2 AND B.C3 IN ('01','02','03','10','11')) AS SUM1, (SELECT SUM(C.t) FROM TEST C WHERE C.C1 = '1' AND C.C2 = A.C2 AND C.C3 IN ('10','11')) AS SUM2 from TEST A GROUP BY A.C2c1,c2,c3,t 就是 code1,....,total这回肯定对了
to bzszq: 我的表里没有code4这个字段啊,运行时出错: ERROR 位于第 2 行: ORA-00904: "CODE4": 无效的标识符
呵呵,吧code4改成total tbname也改成你自己的表名。
to bzszq: 还有一点小问题,根据这条语句: select sum(decode(sign(code3-11),1,0,toal)), sum(decode(sign(10-code3),1,total,0)) from tbname group by code2;结果却是: SUM(DECODE(SIGN(code3-11),1,0,total)) SUM(DECODE(SIGN(10-code3),1,total,0)) --------------------------------- --------------------------------- 15 6 40 21第四个数怎么会是21而不是19呢?
select (SELECT SUM(B.t) FROM TEST B WHERE B.C1 = '1' AND B.C2 = A.C2 AND B.C3 IN ('01','02','03','10','11')) AS SUM1, (SELECT SUM(C.t) FROM TEST C WHERE C.C1 = '1' AND C.C2 = A.C2 AND C.C3 IN ('10','11')) AS SUM2 from TEST A GROUP BY A.C2c1,c2,c3,t 就是 code1,....,total没看到吗???
select sum(decode(sign(code3-11),1,0,toal)), sum(decode(sign(code3-9),1,total,0)) from tbname group by code2;
帮你改了,试试select (SELECT SUM(B.total) FROM table B WHERE B.code1 = '1' AND B.code2 = A.code2 AND B.code3 IN ('01','02','03','10','11')) AS SUM1, (SELECT SUM(C.total) FROM table C WHERE C.code1 = '1' AND C.code2 = A.code2 AND C.code3 IN ('10','11')) AS SUM2 from table A GROUP BY A.C2
没有group by 需求不明确。
from table A
where A.code1='1'
and A.code3 in('01','02','03','10','11')
union all
select sum(A.total),sum(B.total)
from table A
where A.code1='1'
and B.code3 in('10','11');
from table A
where A.code1='1'
and A.code3 in('01','02','03','10','11')
union all
select sum(A.total),sum(A.total)
from table A
where A.code1='1'
and A.code3 in('10','11');
from table A ,table B
where A.code1='1'
and A.code3 in('01','02','03','10','11')
and a.code2=b.code2
union all
select sum(A.total),sum(B.total)
from table A ,table B
where A.code1='1'
and B.code3 in('10','11');
and a.code2=b.code2
from table A,table B
where A.code1='1' and B.code1='1'
and A.code3 in('01','02','03','10','11')
and B.code3 in('10','11')
and A.code2=B.code2
group by code2;
我的意思是说:
对code3等于01~11的情况下,对total求和;还要对code3等于10,11的情况下,对total求和
并且a的code2等于b的code2
通过这个语句,我想得到以下数据:
sum(A.total) sum(B.total)
15 9
40 19也就是:
code1 code2 code3 total 1 01 01 1
1 01 02 2
1 01 03 3
1 01 10 4
1 01 11 5
以上这段的total相加得到15,code1 code2 code3 total
1 01 10 4
1 01 11 5以上这段相加得到9
code1 code2 code3 total 1 02 01 6
1 02 02 7
1 02 03 8
1 02 10 9
1 02 11 10以上这段相加得到40code1 code2 code3 total 1 02 10 9
1 02 11 10以上这段相加得到19最终得到这样的数据:sum(A.total) sum(B.total)
15 9
40 19再麻烦你了:)
sum(decode(sign(10-code3),1,code4,0))
from tbname group by code2;
就有问题了。
(SELECT SUM(B.t)
FROM TEST B
WHERE B.C1 = '1'
AND B.C2 = A.C2
AND B.C3 IN ('01','02','03','10','11')) AS SUM1,
(SELECT SUM(C.t)
FROM TEST C
WHERE C.C1 = '1'
AND C.C2 = A.C2
AND C.C3 IN ('10','11')) AS SUM2
from TEST A
GROUP BY A.C2c1,c2,c3,t 就是 code1,....,total这回肯定对了
我的表里没有code4这个字段啊,运行时出错:
ERROR 位于第 2 行:
ORA-00904: "CODE4": 无效的标识符
tbname也改成你自己的表名。
还有一点小问题,根据这条语句:
select sum(decode(sign(code3-11),1,0,toal)),
sum(decode(sign(10-code3),1,total,0))
from tbname group by code2;结果却是:
SUM(DECODE(SIGN(code3-11),1,0,total)) SUM(DECODE(SIGN(10-code3),1,total,0))
--------------------------------- ---------------------------------
15 6
40 21第四个数怎么会是21而不是19呢?
SUM(DECODE(SIGN(code3-11),1,0,total)) SUM(DECODE(SIGN(10-code3),1,total,0))
--------------------------------- ---------------------------------
15 9
40 19
才对啊
sum(decode(sign(9-code3),1,total,0))
from tbname group by code2;
SUM(DECODE(SIGN(code3-11),1,0,total)) SUM(DECODE(SIGN(10-code3),1,total,0))
--------------------------------- ---------------------------------
15 6
40 21
还是有问题啊,这个效率高吗?
劳驾再帮帮忙啊
(SELECT SUM(B.t)
FROM TEST B
WHERE B.C1 = '1'
AND B.C2 = A.C2
AND B.C3 IN ('01','02','03','10','11')) AS SUM1,
(SELECT SUM(C.t)
FROM TEST C
WHERE C.C1 = '1'
AND C.C2 = A.C2
AND C.C3 IN ('10','11')) AS SUM2
from TEST A
GROUP BY A.C2c1,c2,c3,t 就是 code1,....,total没看到吗???
sum(decode(sign(code3-9),1,total,0))
from tbname group by code2;
您的执行结果为: SUM1 SUM2
---------- ----------
6
21
注:sum2列是空的
(SELECT SUM(B.total)
FROM table B
WHERE B.code1 = '1'
AND B.code2 = A.code2
AND B.code3 IN ('01','02','03','10','11')) AS SUM1,
(SELECT SUM(C.total)
FROM table C
WHERE C.code1 = '1'
AND C.code2 = A.code2
AND C.code3 IN ('10','11')) AS SUM2
from table A
GROUP BY A.C2