有对SQL熟悉的信息朋友没有,
我想查这样两张表,A表 代码agdid,数量akcsl; B 表btgdid代码 bkcsl数量 我想查A,B表代码相同,但是A表的汇总数量不为0,而B表的汇总数量为0,注:AB两表一个代码都有几个数量,要汇总,怎么样查先谢谢了!
我想查这样两张表,A表 代码agdid,数量akcsl; B 表btgdid代码 bkcsl数量 我想查A,B表代码相同,但是A表的汇总数量不为0,而B表的汇总数量为0,注:AB两表一个代码都有几个数量,要汇总,怎么样查先谢谢了!
解决方案 »
- 专用服务器和共享服务器的疑问
- 600万条数据的select count(*)问题。
- 有办法检索一个varchar2型字段存的数据不能转换成yyyy-mm-dd hh23:mi:ss这样的date型吗?
- ORACLE10g的启动问题,请指教!
- 如何删除数据库中的重复记录?????
- 关于Oracle数据类型的问题,在线等!!!
- 关于图像的存储
- 非常急的oracle问题,要多少分给多少!!!!!!!!!!!!!!!!!!!!!!!
- 什么样子的应用需要用到ref Cursor的Procedure?
- oracle standary库重启报错ORA-16032: parameter LOG_ARCHIVE_DEST_3
- oracle 存储过程该咋写
- 安装Oracle9i时只看到Oracle universal installer界面。。。
t2 as (select btgdid,sum(bkcsl) bkcsl rom b group by btgdid)
select * from t1 ,t2 where agdid= btgdid and akcsl<>0 and bkcsl =0;
select a.agdid,a.akcsl,b.bkcsl
from(select agdid,
sum(akcsl) akcsl
from a
group by agdid
)a,
(select btgdid,
sum(bkcsl) bkcsl
from b
group by btgdid
)b
where a.agdid= b.btgdid and a.akcsl<>0 and a.bkcsl =0;
WITH A AS(
SELECT 'A' agdid, 1 akcsl FROM DUAL
UNION ALL
SELECT 'A' , 2 FROM DUAL
UNION ALL
SELECT 'A' , -3 FROM DUAL
UNION ALL
SELECT 'B' , 2 FROM DUAL
UNION ALL
SELECT 'B' , -3 FROM DUAL
UNION ALL
SELECT 'C' , 1 FROM DUAL
UNION ALL
SELECT 'C' , 2 FROM DUAL
UNION ALL
SELECT 'C' , 3 FROM DUAL
),
B AS(
SELECT 'A' btgdid, 1 bkcsl FROM DUAL
UNION ALL
SELECT 'A' , 2 FROM DUAL
UNION ALL
SELECT 'A' , -3 FROM DUAL
UNION ALL
SELECT 'B' , 3 FROM DUAL
UNION ALL
SELECT 'B' , -3 FROM DUAL
UNION ALL
SELECT 'C' , 1 FROM DUAL
UNION ALL
SELECT 'C' , 2 FROM DUAL
UNION ALL
SELECT 'C' , 3 FROM DUAL
)
select a.agdid,a.akcsl,b.bkcsl
FROM
(select agdid,sum(akcsl) akcsl from a group by agdid)a,
(select btgdid,sum(bkcsl) bkcsl from b group by btgdid)b
where a.agdid= b.btgdid and a.akcsl<>0 and b.bkcsl =0;
结果:
AGDID AKCSL BKCSL
--------------------
B -1 0
select agdid,a_sum,b_sum
from (select agdid,sum(akcsl) a_sum from a group by agdid) t,
(select btgdid,sum(bkcsl) b_sum from a group by btgdid) s
where t.agdid=s.btgdid and t.a_sum<>0 and s.b_sum=0
select a.agid,sum(a.akcsl) akcsl,sum(b.bkcsl) bkcsl
from table1 a,table 2 b
where a.agid=b.agid
group by a.agid
having sum(a.akcsl)<>0 and sum(b.bkcsl)=0