有两张表 一张 Q 有 a,b, c 3个字段 int型 。 P 表 有 d,e,f 3个字段 现在要 求 Q 表中 a,b 和 与 P 表 d,e 和 相加 的值 select sum(Q.a+Q.b) + sum(P.d+P.e) as total from P ,Q 为什么算出来 的 值不对
解决方案 »
- "SYSTEM.DBMS_LOGMNR_D" 不存在
- oracle 的实例,我实在理解不了,谁能给我说明一下,谢谢!!
- ORACLE作业查询
- 请问:同一台服务器上装有两个ORACLE数据库实例,通过命令启停数据库,如何区分操作的是哪个数据库实例
- Orcal怎么新建数据库?(一定要详细)
- 新手学ORACLE,看什么书较好?
- ASSERT(!m_bTransactionPending)失败问题,急问!
- 请问,哪里有Oracle9I的下载
- 高手请帮我解决这个问题:ora-00265错误
- 求助!oracle9i里时间比较的函数.急!!
- 上市国企,数据库开发工程师职位-猎头职位,机会不错,可以和我联系,MSN:[email protected],谢谢!!!
- 环境变量
Select a Ttl From Q
Union All
Select b Ttl From Q
Union All
Select d Ttl From P
Union All
Select e Ttl From P
)
Select a+b Ttl From Q
Union All
Select d+e Ttl From P
)
SQL> select sum(col1+col2) from (select sum(Q.a+Q.b) as col1 from Q) x, (select sum(P.d+P.e) as col2 from P) y;SUM(COL1+COL2)
--------------
SQL> insert into q values(1, 2);已创建 1 行。SQL> insert into p values(100, 1);已创建 1 行。SQL> select sum(col1+col2) from (select sum(Q.a+Q.b) as col1 from Q) x, (selec
t sum(P.d+P.e) as col2 from P) y;SUM(COL1+COL2)
--------------
104
(select row_number()over() aa,a.q,q.b from Q)t full join (select row_number()over() ,bb,p.d,p.e from P) tt
on t.aa=tt.bb
insert into t1 values(1,11,1);
insert into t1 values(1,1,11);
insert into t1 values(3,1,2);
create table t2(d int,e int,f int);
insert into t2 values(1,1,1);
insert into t2 values(2,2,5);
insert into t2 values(3,4,2);select sum(b+c)/count(distinct d||','||e||','||f)
+sum(e+f)/count(distinct a||','||b||','||c)
as result from t1,t2;drop table t1;
drop table t2;
/*
RESULT
----------
42
*/
from (select sum(Q.a+Q.b) a from Q) m,(select sum(P.d+P.e) b from P) n
你的做法会因为查两个表的时候,数据少的表会重复加,加到和数据多的表的行数一样
SUM(A)+SUM(B)+SUM(D)+SUM(E)
---------------------------
93
SQL> select sum(a+b) from t1;
SUM(A+B)
----------
18
SQL> select sum(d+e) from t2;
SUM(D+E)
----------
13
SQL> select sum(t) from
2 (select sum(a+b) t from t1
3 union
4 select sum(d+e) from t2
5 );
SUM(T)
----------
31
SQL>
至少有个你想要的结果吧,说出来听听啊,3个字段呢么不是 怎么也要来点关系把。
SUM(A)+SUM(B)+SUM(D)+SUM(E)
---------------------------
93
SQL> select sum(a+b) from t1;
SUM(A+B)
----------
18
SQL> select sum(d+e) from t2;
SUM(D+E)
----------
13
SQL> select sum(t) from
2 (select sum(a+b) t from t1
3 union
4 select sum(d+e) from t2
5 );
SUM(T)
----------
31
SQL> 这是对的