现有表:
id stuNo EssayTitle sort
1 10 A a
2 10 B b
3 11 C c
4 10 D d
检索后想得到:
stuNo Essay
10 A a,B b,D d
11 C c
求这条SQL语句~
------解决方案--------------------
create table 表名(id varchar(10),stuNo varchar(10),EssayTitle varchar(100),sort varchar(100))
go
insert into 表名 select 1,10, 'A ', 'a '
insert into 表名 select 2,10, 'B ', 'b '
insert into 表名 select 3,11, 'C ', 'c '
insert into 表名 select 4,10, 'D ', 'd '
go
--创建一个合并的函数
create function f_merge(@stuNo varchar(100))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(EssayTitle as varchar(100)) + ' ' + cast(sort as varchar(100))
from 表名 where stuNo = @stuNo
set @str = stuff(@str , 1,1, ' ')
return(@str)
End
go
--select * from 表名
--调用自定义函数得到结果：
select stuNo ,dbo.f_merge(stuNo) as Essay from 表名 group by stuNo
drop table 表名
drop function f_merge
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,stuNo varchar(10),EssayTitle varchar(10),sort varchar(10))
insert into tb(id,stuNo,EssayTitle,sort) values(1, '10 ', 'A ', 'a ')
insert into tb(id,stuNo,EssayTitle,sort) values(2, '10 ', 'B ', 'b ')
insert into tb(id,stuNo,EssayTitle,sort) values(3, '11 ', 'C ', 'c ')
insert into tb(id,stuNo,EssayTitle,sort) values(4, '10 ', 'D ', 'd ')
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@stuNo int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(EssayTitle as varchar) + ' ' + cast(sort as varchar) from tb where stuNo = @stuNo
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select distinct stuNo ,dbo.f_hb(stuNo) as Essay from tb
drop table tb
/*
stuNo Essay
---------- -----------
10 A a,B b,D d
11 C c
（所影响的行数为 2 行）
*/