再求一条SQL语句
表 t, 字段 ID(bigint),NO(varchar(50));
MainID ID NO
1 1000 1
2 1000 2
3 1000 3
4 1001 1
5 1001 2
如果我要得到以下的结果,请问如何写SQL:
MainID ID NO
1 1000 1+2+3
2 1000 1+2+3
3 1000 1+2+3
4 1001 1+2
5 1001 1+2
[解决办法]
先创建一个自定义函数：
create function dbo.fn_getNoList(@MainID int, @ID int)
RETURNS varchar(2000)
begin
declare @rst varchar(2000)
set @rst = ' '
select @rst = @rst + '+ ' + [No] from t where MainID=@MainID and [ID]=@ID
if left(@rst,1)= '+ '
set @rst = right(@rst,len(@rst)-1)
RETURN @rst
end
GO
剩下的就简单了，直接这样写即可：
select MainID,[ID],dbo.fn_getNoList(MainID,[ID]) as NoList from t
[解决办法]
create table ty(MainID int, ID int, NO int)
insert ty select 1 ,1000, 1
union all select 2 ,1000, 2
union all select 3 ,1000, 3
union all select 4 ,1001, 1
union all select 5 ,1001, 2
union all select 6 ,-1, 5
union all select 7 ,-1, 7
go
create function getFun(@id int)
returns varchar(50)
as
begin
declare @a varchar(1000)
select @a=coalesce(@a+ '+ ', ' ')+ltrim(no) from ty where id=@id
return @a
end
go
select mainid,id,case when id <> -1 then dbo.getFun(id) else ltrim(no) end NO from ty
