表一
sortid typeid
1 56
1 59
2 62
2 63表二id name sortid typeid
1 张二 1 63
2 王三 2 59
3 小二 1 56那么1和2的记录都有问题,3是正确的,如果用SQL语句查出来呢?
sortid typeid
1 56
1 59
2 62
2 63表二id name sortid typeid
1 张二 1 63
2 王三 2 59
3 小二 1 56那么1和2的记录都有问题,3是正确的,如果用SQL语句查出来呢?
select * from 表二 a where not exists(select * from 表一 b where b.sortid=a.sortid and b.typeid=a.typeid )
insert into @t2 select 1,'张二',1,63 union all
select 2,'王三',2,59 union all
select 3,'小二',1,56
select * from @t2 b where typeid in(select typeid from @t1 a where b.sortid=a.sortid )
结果:
id name sortid typeid
----------- ---------- ----------- -----------
3 小二 1 56(所影响的行数为 1 行)
insert into @t1 select 1,56 union all
select 1,59 union all
select 2,62 union all
select 2,63
前面加上t1表
on t2.sortid=t1.sortid and t2.typeid =t1.typeid
where t1.sortid is null
insert A select 1, 56
union all select 1, 59
union all select 2, 62
union all select 2, 63create table B(id int, name nvarchar(10), sortid int, typeid int)
insert B select 1, '张二', 1, 63
union all select 2, '王三', 2, 59
union all select 3, '小二', 1, 56--正確的數據
select * from B as B
where exists(select * from A where sortid=B.sortid and typeid=B.typeid)--result
id name sortid typeid
----------- ---------- ----------- -----------
3 小二 1 56(1 row(s) affected)--不正確的數據
select * from B as B
where not exists(select * from A where sortid=B.sortid and typeid=B.typeid)
--result
id name sortid typeid
----------- ---------- ----------- -----------
1 张二 1 63
2 王三 2 59(2 row(s) affected)
insert A select 1, 56
union all select 1, 59
union all select 2, 62
union all select 2, 63create table B(id int, name nvarchar(10), sortid int, typeid int)
insert B select 1, '张二', 1, 63
union all select 2, '王三', 2, 59
union all select 3, '小二', 1, 56--正確
select B.* From B,A WHERE B.sortid=A.sortid AND B.typeid=A.typeid
--不正確
select B.* From B WHERE ID Not IN(select B.ID From B,A WHERE B.sortid=A.sortid AND B.typeid=A.typeid)
drop table A,B