34,590
社区成员
发帖
与我相关
我的任务
分享
--创建测试数据
create table 发送表(id int,发送人 nvarchar(20),内容ID int)
insert 发送表 select 1, 'a', 1
union all select 2, 'b', 1
union all select 3, 'c', 1
union all select 4, 'd', 2
union all select 5, 'e', 3
go
create table 内容表(id int,内容 nvarchar(200))
insert 内容表 select 1, 'dsfa'
union all select 2, 'eeeee'
union all select 3, 'dfsfs'
go
-->创建函数1
create function getstr1(@ID int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+',','')+发送人 from 发送表 where @id=内容ID
return @s
end
go
-->创建函数2
create function getstr2(@ID int)
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s+',','')+ltrim(id) from 发送表 where @id=内容ID
return @s
end
go
--SQL查询
select b.ID,b.发送人,a.内容
from 内容表 a
join (select distinct dbo.getstr2(内容ID) ID,内容ID,dbo.getstr1(内容ID) 发送人 from 发送表) b
on a.id=b.内容ID
-->结果
/*
ID 发送人 内容
--------------------
1,2,3 a,b,c dsfa
4 d eeeee
5 e dfsfs
(3 行受影响)
*/
-->删除函数
drop function dbo.getstr1,dbo.getstr2
-->删除测试表
drop table 发送表,内容表
create table 发送表
(id int,发送人 nvarchar(20),内容ID int)
create table 内容表
(id int,内容 nvarchar(200))
insert into 发送表
select 1, 'a', 1
union all
select 2, 'b', 1
union all
select 3, 'c', 1
union all
select 4, 'd', 2
union all
select 5, 'e', 3
insert into 内容表
select 1, 'dsfa'
union all
select 2, 'eeeee'
union all
select 3, 'dfsfs'
select
[id] = stuff( (select ','+ convert(nvarchar(20),id) +'' from 发送表 where 内容ID = a.id for xml path('') ),1,1,''),
[发送人] = stuff( (select ','+ convert(nvarchar(20),发送人) +'' from 发送表 where 内容ID = a.id for xml path('') ),1,1,''),
内容
from 内容表 a
id 发送人 内容
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3 a,b,c dsfa
4 d eeeee
5 e dfsfs
(3 row(s) affected)
--转帖
有表tb, 如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即, group by id, 求 value 的和(字符串相加)
这个问题的一般处理方法是, 写一个聚合函数:
create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+value from tb where id=@id
return stuff(@r,1,1,'')
end
go
-- 调用函数
select id, values=dbo.f_str(id) from tb group by id
这样的问题是,函数不通用,必须为每个要处理的表编写相应的处理函数
在SQL2005中,这个问题的更好解决办法是写一个CLR函数,用于实现字符串的聚合,这样可以解决通用性的问题了。
---------------------------------------------------------------
而在我下面要实现的, 是只用一条SQL语句来完成这个功能
(绝对是一条, 不会是用EXEC()取巧的那种)
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/
--创建测试数据
create table Sender(id int,[Name] nvarchar(20),ContentID int)
insert Sender select 1, 'a', 1
union all select 2, 'b', 1
union all select 3, 'c', 1
union all select 4, 'd', 2
union all select 5, 'e', 3
go
create table [Content](id int,Description nvarchar(200))
insert [Content] select 1,'dsfa'
union all select 2,'eeeee'
union all select 3,'dfsfs'
go
-->获取Name的值
create function dbo.GetSender(@Content varchar(200))
returns varchar(2000)
as
begin
declare @i varchar(2000)
set @i=''
select @i=@i+','+[Name] from Sender S right join [Content] C on S.ContentID=C.id where C.Description=@Content
return stuff(@i,1,1,'')
end
-->获取ID值
create function dbo.getID(@Content varchar(200))
returns varchar(1000)
as
begin
declare @i varchar(2000)
set @i=''
select @i=@i+','+cast(S.id as varchar) from Sender S right join [Content] C on S.ContentID=C.id where C.Description=@Content
return stuff(@i,1,1,'')
end
select distinct(dbo.getID(C.Description)) 'ID',dbo.GetSender(C.Description) as [Name],C.Description from Sender S right join [Content] C on S.ContentID=C.id
select distinct(dbo.getID(Description)) 'ID',dbo.GetSender(Description) as [Name],Description from [Content]
--创建测试数据
create table 发送表(id int,发送人 nvarchar(20),内容ID int)
insert 发送表 select 1, 'a', 1
union all select 2, 'b', 1
union all select 3, 'c', 1
union all select 4, 'd', 2
union all select 5, 'e', 3
go
create table 内容表(id int,内容 nvarchar(200))
insert 内容表 select 1, 'dsfa'
union all select 2, 'eeeee'
union all select 3, 'dfsfs'
go
-->创建函数1
create function getstr1(@ID int)
returns varchar(20)
as
begin
declare @s varchar(20)
select @s=isnull(@s+',','')+发送人 from 发送表 where @id=内容ID
return @s
end
go
-->创建函数2
create function getstr2(@ID int)
returns varchar(20)
as
begin
declare @s varchar(100)
select @s=isnull(@s+',','')+ltrim(id) from 发送表 where @id=内容ID
return @s
end
go
--SQL查询
select dbo.getstr2(ID) ID,dbo.getstr1(ID) 发送人,内容 from 内容表
-->结果
/*
ID 发送人 内容
--------------------
1,2,3 a,b,c dsfa
4 d eeeee
5 e dfsfs
(3 行受影响)
*/
-->删除函数
drop function dbo.getstr1,dbo.getstr2
-->删除测试表
drop table 发送表,内容表
--创建测试数据
create table Sender(id int,[Name] nvarchar(20),ContentID int)
insert Sender select 1, 'a', 1
union all select 2, 'b', 1
union all select 3, 'c', 1
union all select 4, 'd', 2
union all select 5, 'e', 3
go
create table [Content](id int,Description nvarchar(200))
insert [Content] select 1,'dsfa'
union all select 2,'eeeee'
union all select 3,'dfsfs'
go
-->获取Name的值
create function dbo.GetSender(@Content varchar(200))
returns varchar(2000)
as
begin
declare @i varchar(2000)
set @i=''
select @i=@i+','+[Name] from Sender S right join [Content] C on S.ContentID=C.id where C.Description=@Content
return stuff(@i,1,1,'')
end
-->获取ID值
create function dbo.getID(@Content varchar(200))
returns varchar(1000)
as
begin
declare @i varchar(2000)
set @i=''
select @i=@i+','+cast(S.id as varchar) from Sender S right join [Content] C on S.ContentID=C.id where C.Description=@Content
return stuff(@i,1,1,'')
end
select distinct(dbo.getID(C.Description)) 'ID',dbo.GetSender(C.Description) as [Name],C.Description from Sender S right join [Content] C on S.ContentID=C.id
ID Name 内容
--------------------
1,2,3 a,b,c dsfa
4 d eeeee
5 e dfsfs
(3 行受影响)