--好像前几天刚回答过 create table tb (sno int, pno int, qty int) insert tb select 168 , 1 , 5 union all select 168 , 2 , 6 union all select 169 , 1 , 5 union all select 169 , 2 , 5 union all select 169 , 4 , 5 union all select 170 , 1 , 5 union all select 170 , 2 , 5 union all select 170 , 5 , 5 union all select 123 , 1 , 45 --语句 select sno from tb where pno in( select pno from tb where sno=168 ) and sno<>168 group by sno having count(1)>=(select count(1) from tb where sno=168) --结果 sno ----------- 169 170
select sno from 表 a where not exists (select 1 from 表 where sno='168' and a.pno = pno )
select distinct sno from sp s inner join sp p on p.sno=168 and s.pno=p.pno where s.sno<>168
declare @test table(sno int,pno int)insert @test select 168, 1 union all select 168, 2 union all select 169, 1 union all select 169, 2 union all select 169, 4 union all select 170, 1 union all select 170, 2 union all select 170, 5 union all select 123, 1select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno ) /*sno ----------- 169 170(所影响的行数为 2 行) */
--楼上几位的这个是不对的 select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno ) --这个是真包含的情况,如果168和169包含的商品一样的话就不会出结果了 declare @test table(sno int,pno int)insert @test select 168, 1 union all select 168, 2 union all select 169, 1 union all select 169, 2 union all select 170, 1 union all select 170, 2 union all select 170, 5 union all select 123, 1select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno ) --结果sno ----------- 170
如果是全部零件之一那用:select distinct sno from sp where sno<>'168' and pno in (select pno from sp where sno='168')
原来没有时间做,现在想出来了,方法如下, select distinct sno from sptable spx where not exists (select * from sptable spy where spy.sno= '168' and not exists (select * from sptable spz where spz.sno=spx.sno and spz.pno=spy.pno )) 感谢各位
select distinct sno from sptable as A where A.pno in (select pno from sptable as B where b.sno = '168')
from sp aa
where not exists(select 1
from sp
where pno in(select pno from sp where sno='168')
and sno=aa.sno
)
168 1 5
168 2 6
169 1 5
169 2 5
170 1 5
170 2 5
123 1 45
你是步是要差168的,而得出上面的結果啊
create table tb (sno int, pno int, qty int)
insert tb select
168 , 1 , 5 union all select
168 , 2 , 6 union all select
169 , 1 , 5 union all select
169 , 2 , 5 union all select
169 , 4 , 5 union all select
170 , 1 , 5 union all select
170 , 2 , 5 union all select
170 , 5 , 5 union all select
123 , 1 , 45
--语句
select sno from tb where pno in(
select pno from tb where sno=168
) and sno<>168 group by sno having count(1)>=(select count(1) from tb where sno=168)
--结果
sno
-----------
169
170
from sp s inner join sp p on p.sno=168 and s.pno=p.pno
where s.sno<>168
select
168, 1
union all select
168, 2
union all select
169, 1
union all select
169, 2
union all select
169, 4
union all select
170, 1
union all select
170, 2
union all select
170, 5
union all select
123, 1select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno )
/*sno
-----------
169
170(所影响的行数为 2 行)
*/
select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno )
--这个是真包含的情况,如果168和169包含的商品一样的话就不会出结果了
declare @test table(sno int,pno int)insert @test
select
168, 1
union all select
168, 2
union all select
169, 1
union all select
169, 2
union all select
170, 1
union all select
170, 2
union all select
170, 5
union all select
123, 1select sno from @test a where not exists (select 1 from @test where sno='168' and a.pno = pno )
--结果sno
-----------
170
select distinct sno from sptable spx
where not exists
(select * from sptable spy
where spy.sno= '168' and not exists
(select * from sptable spz
where spz.sno=spx.sno and spz.pno=spy.pno ))
感谢各位
from sptable as A
where A.pno in (select pno
from sptable as B
where b.sno = '168')