语句A:select id1 from tb1 where idx1='1234567'
语句B:select id2 from tb2 where idx2='12xysa67'
语句C:select id3 from tb3 where idx3='68544123'
现在要求显示结果D。
D=A查询结果-B查询结果-C查询结果求教,请问大家这该怎么写比较好?是用运算的方式还是使用查询的方式好呢?
有空的朋友请写下示例语句,谢谢。
语句B:select id2 from tb2 where idx2='12xysa67'
语句C:select id3 from tb3 where idx3='68544123'
现在要求显示结果D。
D=A查询结果-B查询结果-C查询结果求教,请问大家这该怎么写比较好?是用运算的方式还是使用查询的方式好呢?
有空的朋友请写下示例语句,谢谢。
-(select id2 from tb2 where idx2='12xysa67')
-(select id3 from tb3 where idx3='68544123')
--2005
select id1 as col from tb1 where idx1='1234567'
except
select id2 as col from tb2 where idx2='12xysa67'
except
select id3 as col from tb3 where idx3='68544123'
where not exists(select 1 from tb2 where idx2='12xysa67' and tb1.关联字段= tb2.关联字段)
and not exists(select 1 from tb3 where idx3='68544123' and tb1.关联字段 = tb2.关联字段)
select id1
from tb1 t
where idx1='1234567'
and not exists (select 1 from tb2 where idx2='12xysa67' and id2 = t.id1)
and not exists (select 1 from tb3 where idx3='68544123' and id3 = t.id1)
2011-1-1 T001 100
2011-1-3 T001 105
2011-1-3 T002 200
2011-1-5 T002 205SELECT * FROM TB1 WHERE DATE = '2011-1-3'--结果
2011-1-3 T001 105
2011-1-3 T002 200SELECT * FROM TB1
EXCEPT
SELECT * FROM TB1 WHERE DATE = '2011-1-3'2011-1-1 T001 100
2011-1-5 T002 205
是不是你想要的?
遗憾的问题出现了,SQL版本是2000,不支持except.
大家能用别的办法帮我实现结果集么?我再明确下问题,问题的含义是从结果集里除去结果,不是加减运算。请会的朋友帮忙多费神下,谢谢了
--sql2000用小三的
select id1
from tb1 t
where idx1='1234567'
and not exists (select 1 from tb2 where idx2='12xysa67' and id2 = t.id1)
and not exists (select 1 from tb3 where idx3='68544123' and id3 = t.id1)
id1
from
tb1
where
idx1='1234567'
and
checksum(id1) not in (select checksum(id2) from tb2 where idx2='12xysa67' )
and
checksum(id1) not in (select checksum(id3) from tb3 where idx3='68544123' )
小三那一段代码也不错。试过也能用,再次感谢大家的帮助!
结贴。