sql语句比较表记录

[chaiwl8869]

3个表：tb1.id,name; tb2.id,f1,f2,f3; tb3.id,f1,f2,f4,f5;3表以id关联，找出tb3比tb2多出来的记录，条件：tb2.id,f1,f2与tb3.id,f1,f2完全相同，并以name，f1,f2,f4,f5显示出来。

[chaiwl8869]

补充：忽略其他字段，谢谢！

[中国风]

select b.name,a.* 
from tb3  as a
	inner join tb1 as b on a.id=b.id
where not exists(select 1 from tb2 where id=a.ID and f1=a.f1 and f2=a.f2)

[chaiwl8869]

我这样做怎么不行？如下：
select tb3.*,tb1.name from tb1 LEFT OUT JOIN tb3 on tb1.id=tb3.id
where not exists(
select 1 from tb2 where tb3.id=tb2.id
and tb3.f1=tb2.f1
and tb3.f2=tb2.f2
)

[chaiwl8869]

2#
难道是我的连接出问题了？
我要明天试试再回复。
谢谢！

[中国风]

[Quote=引用 4 楼 chaiwl8869 的回复:]

2#
难道是我的连接出问题了？
我要明天试试再回复。
谢谢！
[/Quote]

LEFT OUT JOIN

问题出在这里，改为inner join

[yudiw]

思路应该为取两个表的差集：EXCEPT

[yudiw]

select t1.name,t3.* from tb1 as t1 inner join tb3 as t3 on t1.id=t3.id
where t3.id in( select id from ((select id from tb3) except (select id from tb2)) as exceptid )

[chaiwl8869]

LEFT OUT JOIN
问题出在这里，改为inner join
谢谢！按你说的在sql数据库测试通过。
但我后台是oracle 用pl/sql developer 运行以上sql语句报错ora00933:sql命令不正确结束，好像inner join有问题，以下是生产库部分代码，表结构：bill_master.bill_no,id_no,name,visit_date;bill_detail.bill_no,item_no,amount,unit;orders.id_no,order_text,start_date_time,stop_date_time,r_indicator('1'为失效记录、0有效)，找出 bill_detail（300万条）比orders（250万条）多出的记录，麻烦专家再看看问题出在哪：
select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a inner join bill_master b on a.bill_no=b.bill_no
where not exists (
select 1 from bill_orders
where order_text=a.item_name
and id_no=b.id_no
and b.visit_date>=to_date('2011-10-01','yyyy-mm-dd')
and a.item_name<>'白炽灯'
and (
stop_date_time is null or
(
r_indicator='0'
and start_date_time>=to_date('2011-10-01','yyyy-mm-dd')
)
)
)
order by mc

[chaiwl8869]

补充：bill_master.bill_no一对多bill_detail.bill_no，id_no一对一name
运行select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a inner join bill_master b on a.bill_no=b.bill_no
同样报错，最后加‘；’也不行

[chaiwl8869]

解决了，sql和oracle数据库差别还是很大的

select b.id_no,b.name,b.visit_date,a.item_name as mc,a.amount,a.units
from bill_detail a, bill_master b
where a.bill_no=b.bill_no
and b.visit_date>=to_date('2011-10-01','yyyy-mm-dd')
and a.item_name<>'白炽灯'
and not exists ( select 1 from bill_orders
where order_text=a.item_name
and id_no=b.id_no
and ( stop_date_time is null or
( r_indicator='0'
and start_date_time>=to_date('2011-10-01','yyyy-mm-dd')
) ) )
order by mc