在sqlserver 2000中这个sql怎么写呢?一个表中存 taskid,taskname(1,需求;2,设计;3,测试),一个表中存在 taskid,resourcename,(1,pear;1,dong;2,peardong) 我想找到任务 taskid,taskname,resourcename(1,需求,pear、dong;2,设计,peardong;3,测试,null) sql语句怎么写呢? 谢谢
sql语句,谢谢!
sql语句,谢谢!
go
create table [ta]([taskid] int,[taskname] varchar(4))
insert [ta]
select 1,'需求' union all
select 2,'设计' union all
select 3,'测试'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([taskid] int,[resourcename] varchar(8))
insert [tb]
select 1,'pear' union all
select 1,'dong' union all
select 2,'peardong'
go-->函数
create function f_str(@id int)
returns varchar(50)
as
begin
declare @s varchar(50)
select @s=isnull(@s+',','')+resourcename
from tb
where taskid=@id
return @s
end-->查询
select ta.*,tb.resourcename
from ta
left join (select taskid,dbo.f_str(taskid) as resourcename from tb group by taskid) tb
on ta.taskid=tb.taskid
--测试结果:
/*
taskid taskname resourcename
----------- -------- --------------------------------------------------
1 需求 pear,dong
2 设计 peardong
3 测试 NULL(3 行受影响)*/
2005可以用XML