22,210
社区成员
发帖
与我相关
我的任务
分享
select distinct(id) from #a where id not in
(
select distinct c.id from #a right join
(select a.*,b.* from #b b, (select distinct(id) from #a)a)c on c.pid=#a.pid and c.num=#a.num and #a.id=c.id
where #a.id is null
)
id
-----------
1
2
(2 行受影响)
create table A(id int, pid int, num int)
create table B(pid int, num int)
insert A select 1, 2, 5
union all select 1, 3, 7
union all select 1, 4, 9
union all select 4, 2, 5
union all select 5, 4, 9
union all select 6, 3, 7
union all select 2, 2, 5
union all select 2, 3, 7
union all select 2, 4, 9
union all select 2, 2, 5
insert B select 2, 5
union all select 3, 7
union all select 4, 9
--如果一个pid可以对应多个num
select distinct id
from A
where not exists(select 1 from B
left join (select pid, num from A A1 where id=A.id) T
on B.pid=T.pid and B.num=T.num
where T.pid is null)
/*
id
-----------
1
2
(2 row(s) affected)
*/
drop table A,B
set nocount on
declare @ta table(id int,pid int,num int)
insert @ta select 1 ,2, 5
insert @ta select 1 ,3, 7
insert @ta select 1 ,4 ,9
insert @ta select 4, 2 ,5
insert @ta select 5 ,4 ,9
insert @ta select 6 ,3 ,7
insert @ta select 2 ,2, 5
insert @ta select 2, 3 ,7
insert @ta select 2 ,4 ,9
insert @ta select 2, 2, 5
declare @tb table(pid int,num int)
insert @tb select 2,5
insert @tb select 3 ,7
insert @tb select 4, 9
select distinct id
from @ta a
where not exists(select 1
from @tb b
where not exists (select 1 from @ta T where id=A.id and pid = b.pid and num = b.num))
/*
id
-----------
1
2
*/
create table A(id int, pid int, num int)
create table B(pid int, num int)
insert A select 1, 2, 5
union all select 1, 3, 7
union all select 1, 4, 9
union all select 4, 2, 5
union all select 5, 4, 9
union all select 6, 3, 7
union all select 2, 2, 5
union all select 2, 3, 7
union all select 2, 4, 9
union all select 2, 2, 5
insert B select 2, 5
union all select 3, 7
union all select 4, 9
select distinct id
from A
where not exists(select pid from B where pid not in (select pid from A T where id=A.id))
/*
id
-----------
1
2
*/
drop table A,B
--try
select distinct id
from A
where not exists(select pid from B where pid not in (select pid from A T where id=A.id))