我有两张表
A表中有 nos,gxrq两个字段,
B表中有 nos,gxrq两个字段,
需求是这样的:
我要根据传入的单号,比如是Test001, 把A表中单号是Test001, 该单号在B表中不存在/存在但gxrq不同的查出来
上面是两种情况,用SQL该如何写呢?
A表中有 nos,gxrq两个字段,
B表中有 nos,gxrq两个字段,
需求是这样的:
我要根据传入的单号,比如是Test001, 把A表中单号是Test001, 该单号在B表中不存在/存在但gxrq不同的查出来
上面是两种情况,用SQL该如何写呢?
select * from A where nos='TEST001' and exists (select 1 from B where nos ='Test001' and A.gxrq<>B.gxrq)
select a.* from a
where nos='Test001'
and
(not exists(select 1 from b where nos=a.nos)
or
exists(select 1 from b where nos=b.nos and gxrq!=a.gxrq)
)
select *
from a
where exists (select 1 from b where nos = a.nos and gxrq <> a.gxrq)
or not exists (select 1 from tb where nos = a.nos)
?
and
( not exists (select top 1 * from B表 b where b.nos=a.nos)
or
exists (select top 1 * from B表 b where b.nos=a.nos and a.gxrq<>b.gxrq)
)
--得到一個具體的單號 @nos 和 gxrq 值 @gxrq 傳輸的。
if exists (select 1 from b where nos = @nos and gxrq <> @gxrq)
or not exists (select 1 from tb where nos = @nos)
--傳輸處理--或者--傳輸處理的SQL
insert into ...
select ...
from a,...
where a.nos = '' and ....
and exists (select 1 from b where nos = a.nos and gxrq <> a.gxrq) --加where條件後邊
or not exists (select 1 from tb where nos = a.nos)
or
exists(select nos from b where nos=b.nos and gxrq!=a.gxrq)
)
or
exists(select nos from b where nos=b.nos and gxrq!=a.gxrq)
)
from a
where exists (select 1 from b where nos = a.nos and gxrq <> a.gxrq)
or not exists (select 1 from tb where nos = a.nos)
CREATE TABLE TB(NOS VARCHAR(20),GXRQ INT)INSERT INTO TA VALUES('Test001',1),('Test002',1),('Test003',1)
INSERT INTO TB VALUES('Test004',1),('Test002',1),('Test003',2)SELECT * FROM TA A
WHERE EXISTS(SELECT * FROM TB B WHERE A.NOS=B.NOS AND A.GXRQ<>B.GXRQ )
OR NOT EXISTS(SELECT * FROM tb B WHERE A.NOS=B.NOS AND A.GXRQ=B.GXRQ)