完全匹配问题

[internetroot]

/*
完全匹配问题
测试数据
产品 厂家
---------------
a1 a
a2 a
a3 a
b1 b
b2 b
a1 c
a2 c
a3 c
b1 d
a1 d
a2 d
a3 d
e1 e
a1 s

输入参数('a1','a2','a3')应得到
a
c

输入('a1','a2')应没有返回值

输入('b1','b2')应返回
b

即输入一个产品列表返回刚好生产（不多生产一种也不少生产一种产品）这些产品的厂家

最好不要使用到group统计语句，且要考虑效率问题，谢谢！
*/

[wuxinyuzhu]

呵呵 帮D

[中国风]

传参时有没有引号的'b1','b2'

[internetroot]

我的意思是用型如：

select ......
in ('a1','a2','a3')

的方式查询出来

[中国风]

select 
	[厂家]
from T a
where [产品] in('a1','a2','a3')
group by [厂家]
having count(1)=(select count(1) from t where [厂家]=a.[厂家]) and count(1)=3

[中国风]

count(1)=3--取传参记录

[hery2002]

.....

[hery2002]

[Quote=引用 4 楼 roy_88 的回复:]
select
[厂家]
from T a
where [产品] in('a1','a2','a3')
group by [厂家]
having count(1)=(select count(1) from t where [厂家]=a.[厂家]) and count(1)=3
[/Quote]
呵呵,不是说不用group by 么?

[中国风]

select 
[厂家]
from 
T a
where 
not exists (Select 	1 from 	T b where [厂家]=a.[厂家]
and not exists(select 1 from T where [产品]=b.[产品] and [产品] in('a1','a2','a3')))
group by [厂家]
having count(1)=(select count(distinct [产品]) from T where [产品] in('a1','a2','a3'))

[azhhuoiu]

declare @tb table (x varchar(10),y varchar(10))
insert into @tb select 'a1' ,'a'
union all select 'a2', 'a'
union all select 'a3', 'a'
union all select 'b1', 'b'
union all select 'b2', 'b'
union all select 'a1', 'c'
union all select 'a2', 'c'
union all select 'a3', 'c'
union all select 'b1', 'd'
union all select 'a1', 'd'
union all select 'a2', 'd'
union all select 'a3', 'd'
union all select 'e1', 'e'
union all select 'a1', 's'

select y ,count(x) from @tb group by y HAVING COUNT(CASE WHEN x IN('a1','a2','a3') THEN 1 ELSE NULL END)=3 and count(x)=3

[中国风]

呵呵,不是说不用group by 么?
--------------
05可以

[中国风]

select [厂家] 
from T a
 where exists(select 1 from T where [厂家]=a.[厂家] and [产品] in('a1','a2','a3'))
group by [厂家]
having count(1)=(select count(distinct [产品]) from T where   [产品] in('a1','a2','a3'))

[中国风]

以上写法都可以

--> --> (Roy_88)生成測試數據
 
if not object_id('T') is null
	drop table T
Go
Create table T([产品] nvarchar(2),[厂家] nvarchar(1))
Insert T
select N'a1',N'a' union all
select N'a2',N'a' union all
select N'a3',N'a' union all
select N'b1',N'b' union all
select N'b2',N'b' union all
select N'a1',N'c' union all
select N'a2',N'c' union all
select N'a3',N'c' union all
select N'b1',N'd' union all
select N'a1',N'd' union all
select N'a2',N'd' union all
select N'a3',N'd' union all
select N'e1',N'e' union all
select N'a1',N's'
Go

print 'test1'
select [厂家] 
from T a
 where exists(select 1 from T where [厂家]=a.[厂家] and [产品] in('a1','a2','a3'))
group by [厂家]
having count(1)=(select count(distinct [产品]) from T where   [产品] in('a1','a2','a3'))

print 'test2'

select [厂家] 
from T a
 where exists(select 1 from T where [厂家]=a.[厂家] and [产品] in('a1','a2'))
group by [厂家]
having count(1)=(select count(distinct [产品]) from T where   [产品] in('a1','a2'))

print 'test3'

select [厂家] 
from T a
 where exists(select 1 from T where [厂家]=a.[厂家] and [产品] in('b1','b2'))
group by [厂家]
having count(1)=(select count(distinct [产品]) from T where   [产品] in('b1','b2'))



(14 行受影响)
test1
厂家
----
a
c

(2 行受影响)

test2
厂家
----

(0 行受影响)

test3
厂家
----
b

(1 行受影响)

[cyc_cheng]

哈哈，写个函数，预设多个参数值，函数里面一个个select,不用group by

不过用group by简单省事啊，为啥不用呢

[cyc_cheng]

写个函数通用，如果查询的参数变一次，写个select语句太死板了