表B
ID(FK) Memos
001 football
001 sing
001 internet
查询结果需要是
ID Name Memos
001 lid football,sing,internet
...全文
608打赏收藏
求一sql,有点难度,等待...
注意:出于执行效率的考虑,不用游标(循环), 表A ID(主键) Name 001 lid 002 like 表B ID(FK) Memos 001 football 001 sing 001 internet 查询结果需要是 ID Name Memos 001 lid football,sing,internet
1,建自定义函数
Create function getstr(@id Nchar(100))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(4000),@分隔符 Nvarchar(10)
set @分隔符=N','
set @str=N''
select @str=@str+rtrim(Col)+@分隔符 from 表名
--------------------------------^^^^要相加的字段名
where 相加条件字段=@id
set @str=left(@str,len(@str)-1)落 ---却除最后一个分隔符
return @str
end
GO
2,调用
select 条件字段,dbo.getstr(条件字段) from 表名 group by 条件字段
create table a ( id varchar(100),name varchar(100))
insert into a values('001','lid')
insert into a values('002','like')
insert into a values('001','d')
create table b(id varchar(100), Memos varchar(100))
insert into b values('001', 'football')
insert into b values('001', 'sing')
create function fun_1(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+Memos from b where ID=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
select id,dbo.fun_1(id) as name into #tem
from b
group by id
go
select a.id,a.name,t.name
from a join #tem as t on a.id=t.id
go
create function getresult(@ID varchar(10))
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
select @result=@result+Name from B where ID=@ID
return @result
end
语句:
select id,[name] ,dbo.getresult(ID) from A
where id in (select distinct id from B)
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+Memos from 表B where ID=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select id,name,dbo.fmerg(id) from 表A
where id in (select distinct id from 表B)