-->生成测试数据
drop table tb
go
create table tb([LINE_CODE] nvarchar(6),[AIR_CODE] nvarchar(2))
Insert tb
select N'SYXKWE',N'HU' union all
select N'SYXKWE',N'CA' union all
select N'SYXKWE',N'CZ' union all
select N'SYXKWE',N'8L' union all
select N'SYXKWE',N'MU' union all
select N'CANTYN',N'HU' union all
select N'CANTYN',N'8L' union all
select N'CANTYN',N'CZ' union all
select N'CANTYN',N'CA' union all
select N'TNACSX',N'HU' union all
select N'TNACSX',N'MU'DECLARE @air_code nvarchar(200)
set @air_code='HU,CA,MU'SELECT * FROM (Select LINE_CODE,COUNT(1) AS CNT from tb WHERE charindex(','+AIR_CODE+',',','+@air_code+',')>0
GROUP BY LINE_CODE) A
WHERE CNT=3
drop table tb
go
create table tb([LINE_CODE] nvarchar(6),[AIR_CODE] nvarchar(2))
Insert tb
select N'SYXKWE',N'HU' union all
select N'SYXKWE',N'CA' union all
select N'SYXKWE',N'CZ' union all
select N'SYXKWE',N'8L' union all
select N'SYXKWE',N'MU' union all
select N'CANTYN',N'HU' union all
select N'CANTYN',N'8L' union all
select N'CANTYN',N'CZ' union all
select N'CANTYN',N'CA' union all
select N'TNACSX',N'HU' union all
select N'TNACSX',N'MU'DECLARE @air_code nvarchar(200)
set @air_code='HU,CA,MU'SELECT * FROM (Select LINE_CODE,COUNT(1) AS CNT from tb WHERE charindex(','+AIR_CODE+',',','+@air_code+',')>0
GROUP BY LINE_CODE) A
WHERE CNT=3
DECLARE @tb table([LINE_CODE] nvarchar(6),[AIR_CODE] nvarchar(2))
Insert @tb
select N'SYXKWE',N'HU' union all
select N'SYXKWE',N'CA' union all
select N'SYXKWE',N'CZ' union all
select N'SYXKWE',N'8L' union all
select N'SYXKWE',N'MU' union all
select N'CANTYN',N'HU' union all
select N'CANTYN',N'8L' union all
select N'CANTYN',N'CZ' union all
select N'CANTYN',N'CA' union all
select N'TNACSX',N'HU' union all
select N'TNACSX',N'MU'DECLARE @air_code nvarchar(200)
set @air_code='HU,CA,MU'SELECT LINE_CODE FROM
(Select LINE_CODE,COUNT(1) AS CNT from @tb
WHERE charindex(','+AIR_CODE+',',','+@air_code+',')>0
GROUP BY LINE_CODE) A
WHERE CNT=3 -- 3为air_code的个数,可以写个函数来返回就可以了,
/*
LINE_CODE
---------
SYXKWE
*/
declare @t table (lc varchar(10),ac varchar(10))
insert into @t
select 'SYXKWE','HU' union
select 'SYXKWE','CA' union
select 'SYXKWE','CZ' union
select 'SYXKWE','8L' union
select 'SYXKWE','MU' union
select 'CANTYN','HU' union
select 'CANTYN','8L' union
select 'CANTYN','CZ' union
select 'CANTYN','CA' union
select 'TNACSX','HU' union
select 'TNACSX','MU'declare @AIR_CODE varchar(20)
set @AIR_CODE='HU,MA,CU'select *
from (select distinct lc from @t where charindex(','+ac+',',','+@AIR_CODE+',')>0) a
where not exists (select 1
from @t b
left join @t c on a.lc=c.lc and b.ac=c.ac
where charindex(','+b.ac+',',','+@AIR_CODE+',')>0 and c.lc is null
)/*
lc
----------
CANTYN
SYXKWE
TNACSX(3 行受影响)
*/
http://topic.csdn.net/u/20080804/15/181b17a1-904e-4c16-aa33-e55223fea793.html?299862888
DECLARE @tb table([LINE_CODE] nvarchar(6),[AIR_CODE] nvarchar(2))
Insert @tb
select N'SYXKWE',N'HU' union all
select N'SYXKWE',N'CA' union all
select N'SYXKWE',N'CZ' union all
select N'SYXKWE',N'8L' union all
select N'SYXKWE',N'MU' union all
select N'CANTYN',N'HU' union all
select N'CANTYN',N'8L' union all
select N'CANTYN',N'CZ' union all
select N'CANTYN',N'CA' union all
select N'TNACSX',N'HU' union all
select N'TNACSX',N'MU' DECLARE @s nvarchar(200)
set @s='HU,CA,MU' select LINE_CODE from @tb where charindex(','+AIR_CODE+',',',' + @s + ',')>0
group by LINE_CODE having count(distinct AIR_CODE)=(len(@s) - len(replace(@s,',',''))+1)
---------------
SYXKWE