有记录如下:
people code name
A 001 二甲
A 002 唐平
A 003 消炎药
A 004 甘精
B 002 唐平
B 001 二甲
B 005 红霉素我想把同时用过 '唐平','甘精'(可以再加N种)符合条件的people找出来
比如本例中输出:
A希望不要用交叉表按group by people 把name也串成一起,再来查询...
people code name
A 001 二甲
A 002 唐平
A 003 消炎药
A 004 甘精
B 002 唐平
B 001 二甲
B 005 红霉素我想把同时用过 '唐平','甘精'(可以再加N种)符合条件的people找出来
比如本例中输出:
A希望不要用交叉表按group by people 把name也串成一起,再来查询...
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CLASS VARCHAR(50)
SELECT @CLASS=ISNULL(@CLASS+',','')+LTRIM(WAIT_USER) FROM TTB WHERE ID=@ID
RETURN @CLASS
END字符合并函数
where name in('唐平','甘精')
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 個資料列受到影響)
*/
-- 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 行)
*/上面弄错了
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 個資料列受到影響)
*/
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
比如还有一个表t2
code Pname
A 小张
B 小李需要进行关联查询
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 個資料列受到影響)*/
select *
from (select * from tb where name='唐平') as a
where a.name='甘精'
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