select ItemCode from
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
) t
group by ItemCode having count(*) = 2select ItemCode from
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
union all
select distinct ItemCode from tb where BomItemCode = 3
) t
group by ItemCode having count(*) = 3
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
) t
group by ItemCode having count(*) = 2select ItemCode from
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
union all
select distinct ItemCode from tb where BomItemCode = 3
) t
group by ItemCode having count(*) = 3
insert into tb values(1 , 1.01 , '20PC' , 1 , 1 , 6 , '只')
insert into tb values(2 , 1.01 , '20PC' , 2 , 2 , 6 , '只')
insert into tb values(3 , 1.01 , '20PC' , 3 , 3 , 3 , '只')
insert into tb values(4 , 1.01 , '20PC' , 4 , 4 , 3 , '只')
insert into tb values(5 , 1.01 , '20PC' , 5 , 5 , 2 , '只')
insert into tb values(6 , 1.02 , '15PC' , 1 , 1 , 5 , '只')
insert into tb values(7 , 1.02 , '15PC' , 2 , 2 , 5 , '只')
insert into tb values(8 , 1.02 , '15PC' , 5 , 5 , 5 , '只')
insert into tb values(9 , 1.03 , '12PC' , 1 , 1 , 4 , '只')
insert into tb values(10, 1.03 , '12PC' , 2 , 2 , 4 , '只')
insert into tb values(11, 1.03 , '12PC' , 4 , 5 , 4 , '只')
goselect ItemCode from
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
) t
group by ItemCode having count(*) = 2
/*
ItemCode
--------------------
1.01
1.02
1.03(所影响的行数为 3 行)
*/
select ItemCode from
(
select distinct ItemCode from tb where BomItemCode = 1
union all
select distinct ItemCode from tb where BomItemCode = 2
union all
select distinct ItemCode from tb where BomItemCode = 3
) t
group by ItemCode having count(*) = 3
/*
ItemCode
--------------------
1.01(所影响的行数为 1 行)
*/drop table tb
select distinct itemcode from t where bomitemcode in (1,2,3)
========================================
http://www.dbtuning.cn
主营:中小企业数据库管理、优化、调校服务
========================================
declare @t table(RecID int,ItemCode varchar(4),ItemName varchar(4),BomItemCode int,BomItemName int,BomQty int,UnitsName varchar(4))
insert into @t select 1 ,'1.01','20PC',1,1,6,'只'
insert into @t select 2 ,'1.01','20PC',2,2,6,'只'
insert into @t select 3 ,'1.01','20PC',3,3,3,'只'
insert into @t select 4 ,'1.01','20PC',4,4,3,'只'
insert into @t select 5 ,'1.01','20PC',5,5,2,'只'
insert into @t select 6 ,'1.02','15PC',1,1,5,'只'
insert into @t select 7 ,'1.02','15PC',2,2,5,'只'
insert into @t select 8 ,'1.02','15PC',5,5,5,'只'
insert into @t select 9 ,'1.03','12PC',1,1,4,'只'
insert into @t select 10,'1.03','12PC',2,2,4,'只'
insert into @t select 11,'1.03','12PC',4,5,4,'只'
select
distinct ItemCode
from
@t t
where
t.BomItemCode in(1,2)
and
exists(select * from @t where ItemCode=t.ItemCode and BomItemCode in(1,2) having count(distinct BomItemCode)=2)
/*
ItemCode
--------
1.01
1.02
1.03
*/select
distinct ItemCode
from
@t t
where
t.BomItemCode in(1,2,3)
and
exists(select * from @t where ItemCode=t.ItemCode and BomItemCode in(1,2,3) having count(distinct BomItemCode)=3)
/*
ItemCode
--------
1.01
*/