问下高手，有点难度的查询

[jacobwc]

有记录如下:
people code name
A 001 二甲
A 002 唐平
A 003 消炎药
A 004 甘精
B 002 唐平
B 001 二甲
B 005 红霉素

我想把同时用过 '唐平','甘精'(可以再加N种)符合条件的people找出来
比如本例中输出:
A

希望不要用交叉表按group by people 把name也串成一起，再来查询...

[bancxc]

select distict people from tb 
where name in('唐平','甘精')

[SQL77]

CREATE  FUNCTION GET_STRING(@ID INT)
RETURNS VARCHAR(50)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(50)
       SELECT @CLASS=ISNULL(@CLASS+',','')+LTRIM(WAIT_USER) FROM TTB WHERE ID=@ID
       RETURN @CLASS
  END

字符合并函数

[水族杰纶]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (people nvarchar(2),code nvarchar(6),name nvarchar(6))
insert into [tb]
select 'A','001',N'二甲' union all
select 'A','002',N'唐平' union all
select 'A','003',N'消炎药' union all
select 'A','004',N'甘精' union all
select 'B','002',N'唐平' union all
select 'B','001',N'二甲' union all
select 'B','005',N'红霉素'
select distinct people from tb t 
where exists(select 1 from tb where people=t.people and name=N'唐平')
and exists(select 1 from tb where people=t.people and name=N'甘精')
/*
people
------
A

(1 個資料列受到影響)
*/

[jacobwc]

丢人啊。。。
忘了in了

[jacobwc]

3L的太复杂了，正是我现在用的方法

[--小F--]

in的效率不是很高 还是exists好

[SQL77]

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-15 16:34:52
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
--	Aug  6 2000 00:57:48 
--	Copyright (c) 1988-2000 Microsoft Corporation
--	Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
go 
create table [TB]([people] varchar(1),[code] varchar(3),[name] varchar(6))
insert [TB]
select 'A','001','二甲' union all
select 'A','002','唐平' union all
select 'A','003','消炎药' union all
select 'A','004','甘精' union all
select 'B','002','唐平' union all
select 'B','001','二甲' union all
select 'B','005','红霉素'
--------------开始查询--------------------------
DECLARE @NAME NVARCHAR(4000)
SET @NAME='唐平,甘精'
SELECT people FROM TB WHERE CHARINDEX(','+name+',',','+@NAME+',')>0 
GROUP BY people 
HAVING SUM(CASE WHEN CHARINDEX(','+name+',',','+@NAME+',')>0 THEN 1 ELSE 0 END)
=LEN(@NAME)-LEN(REPLACE(@NAME,',',''))+1
----------------结果----------------------------
/* 

（所影响的行数为 7 行）

people 
------ 
A

（所影响的行数为 1 行）


*/

上面弄错了

[水族杰纶]

[Quote=引用 5 楼 jacobwc 的回复:]
3L的太复杂了，正是我现在用的方法
[/Quote]
In 結果未比對唷

[水族杰纶]

[Quote=引用 4 楼 jacobwc 的回复:]
丢人啊。。。
忘了in了
[/Quote]

--結果不是你想要的
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (people nvarchar(2),code nvarchar(6),name nvarchar(6))
insert into [tb]
select 'A','001',N'二甲' union all
select 'A','002',N'唐平' union all
select 'A','003',N'消炎药' union all
select 'A','004',N'甘精' union all
select 'B','002',N'唐平' union all
select 'B','001',N'二甲' union all
select 'B','005',N'红霉素'
select distinct people from tb 
where name in('唐平','甘精')
/*
people
------
A
B

(2 個資料列受到影響)
*/

[lidanzi]

declare @t table(people char(1),code varchar(10),name varchar(20))
insert @t
select 'A'  ,      '001'  ,      '二甲'  union all
select 'A'  ,      '002'  ,      '唐平'  union all
select 'A'  ,      '003'  ,      '消炎药'  union all
select 'A' ,       '004'  ,      '甘精'  union all
select 'B' ,       '002'  ,      '唐平'  union all
select 'B' ,       '001'  ,      '二甲'  union all
select 'B' ,       '005'  ,      '红霉素'

select people from @t 
where name in('唐平','甘精')
group by people
having count(1)=2

[jacobwc]

in结果是错的。。。

[jacobwc]

还有一个最重要的，需要和别的表进行关联查询，
比如还有一个表t2
code Pname
A 小张
B 小李

需要进行关联查询。。。

[水族杰纶]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (people nvarchar(2),code nvarchar(6),name nvarchar(6))
insert into [tb]
select 'A','001',N'二甲' union all
select 'A','002',N'唐平' union all
select 'A','003',N'消炎药' union all
select 'A','004',N'甘精' union all
select 'B','002',N'唐平' union all
select 'B','001',N'二甲' union all
select 'B','005',N'红霉素'
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (code nvarchar(2),Pname nvarchar(4))
insert into [ta]
select 'A',N'小张' union all
select 'B',N'小李'
select distinct
       people,
       pname
from tb t ,ta a 
where t.people=a.code and exists(select 1 from tb where people=t.people and name=N'唐平')
and exists(select 1 from tb where people=t.people and name=N'甘精')
/*
people pname
------ -----
A      小张

(1 個資料列受到影響)

*/

[J1985Z]

select  * 
from (select * from tb where name='唐平') as a
where a.name='甘精'

[envykok]

DECLARE @tempTable Table
(
  PeopleID CHAR(10),
  Code CHAR(10),
  Name CHAR(10)
)

INSERT INTO @tempTable
SELECT 'A','001','二甲' union all
SELECT 'A','002','唐平' union all
SELECT 'A','003','消炎药' union all
SELECT 'A','004','甘精' union all
SELECT 'B','002','唐平' union all
SELECT 'B','001','二甲'

SELECT t.PeopleID
FROM @tempTable t
GROUP BY  t.PeopleID
HAVING SUM(CASE WHEN code='002' AND Name='唐平' THEN 1 
			   WHEN code='004' AND Name='甘精' THEN 1
		  ELSE 0 END)=2