有表结构及数据如下:
CustomerID Value
1 a
1 b
1 c
1 d
2 b
2 c
2 d
2 e
3 c
3 d
3 e
3 f
求一SQL语句,查询出含有多个value值的CustomerID
如: 查询含有a,b的,则CustomerID为1
查询含有b,c的,则CustomerID为1,2
查询含有c,d的,则CustomerID为1,2,3
CustomerID Value
1 a
1 b
1 c
1 d
2 b
2 c
2 d
2 e
3 c
3 d
3 e
3 f
求一SQL语句,查询出含有多个value值的CustomerID
如: 查询含有a,b的,则CustomerID为1
查询含有b,c的,则CustomerID为1,2
查询含有c,d的,则CustomerID为1,2,3
go
insert into tbltest
select 1,'A' union all
select 1,'B' union all
select 1,'C' union all
select 1,'F' union all
select 1,'G' union all
select 2,'E' union all
select 2,'F' union all
select 2,'F'
go--写一个聚合函数:
create function dbo.fn_Merge(@F1 int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+列C from tbltest where 列A=@F1 -- 先加逗号,再串起来
return stuff(@r,1,1,'') -- 删除最前面加的逗号
end
go-- 调用函数
select 列A, dbo.fn_Merge(列A) as 列C
from tbltest
group by 列A
go--删除测试数据
drop table tbltest
drop function fn_Merge--查看结果
/*
列A 列C
1 A,B,C,F,G
2 E,F,F
*/
go
insert into tbltest
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 2,'b' union all
select 2,'c' union all
select 2,'d' union all
select 2,'e' union all
select 3,'c' union all
select 3,'d' union all
select 3,'e' union all
select 3,'f'
go--写一个聚合函数:
create function dbo.fn_Merge(@F1 varchar(100))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+CustomerID from tbltest where Value=@F1
return stuff(@r,1,1,'')
end
go-- 调用函数
select Value, dbo.fn_Merge(Value) as CustomerID
from tbltest
group by Value
go--删除测试数据
drop table tbltest
drop function fn_Merge--查看结果
/*
Value CustomerID
a 1
b 1,2
c 1,2,3
d 1,2,3
e 2,3
f 3
*/
--用distinct 去掉重复值
returns varchar(4000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+CustomerID from table where Value=@value
set @str=substring(@str,2,len(@str))
return @str
end
go
select value,dbo.fn_getstr(value) as CustomerID
from table
go
a,b
a,b,c
a
a,b,c,d
a,b,d,d,e,f
gc_ding(施主,给个妞泡好么) 做对了第二种问法,第二楼的人做对了
FROM 表
WHERE COUNT(*)>1
GROUP BY CustomerID
insert T select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'union all select 2,'b'
union all select 2,'c'
union all select 2,'d'
union all select 2,'e'union all select 3,'c'
union all select 3,'d'
union all select 3,'e'
union all select 3, 'f'create function fun(@str varchar(100))
returns varchar(100)
as
begin
declare @re varchar(100)
set @re='' declare @len int
set @len=len(@str)-len(replace(@str, ',', ''))+1 select @re=@re+','+rtrim(CustomerID) from T where charindex(Value, @str)>0 group by CustomerID having count(*)=@len
return(stuff(@re, 1, 1, ''))
end
select dbo.fun('a,b')
--result
----------------------------------------------------------------------------------------------------
1(1 row(s) affected)
select dbo.fun('b,c')
--result
----------------------------------------------------------------------------------------------------
1,2(1 row(s) affected)select dbo.fun('c,d')
--result
----------------------------------------------------------------------------------------------------
1,2,3(1 row(s) affected)
我们一起创业,我们一起发财,我们一起快乐,我们一起努力!
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 2,'b' union all
select 2,'c' union all
select 2,'d' union all
select 2,'e' union all
select 3,'c' union all
select 3,'d' union all
select 3,'e' union all
select 3,'f'Select customerid From (Select customerid From @tab Where value = 'a' or value='b')c
group by customerid
having count(customerid)>1