sql 查询一对多关系

作者:freethy 发布于:2012-1-6 12:29 Friday

我怎样查询出来
id    cindex
10    21,22
11     16,17

 
//详解。。。。。。。。。。。。。。。。

create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

 
create function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end
go
--调用函数
select id , value = dbo.f_str(id) from tb group by id

Powered by emlog 苏ICP备11068260号-7