--字符串合并 CREATE FUNCTION dbo.f_tb(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str + '、' + resourcename FROM tb1 WHERE taskid=@id RETURN STUFF(@str, 1, 1, '') END GO select tb2.taskid,taskname,resourcename from tb2 left join ( SELECT taskid, dbo.f_tb(taskid) as resourcename FROM tb1 GROUP BY taskid) t on tb2.taskid=t.taskid
--2005的方法 select tb2.taskid,taskname,resourcename from tb2 left join ( select resourcename =stuff((select '|'+resourcename from tb1 t where taskid=tb.taskid for xml path('')), 1, 1, ''),taskid from tb1 group by taskid ) t on tb2.taskid=t.taskid
用 right join 就行了 第一个表 a right Join 第二个表 b on a.taskid=b.taskid
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-27 22:14:40 ---------------------------------------------------------------- --> 测试数据:[ta] if object_id('[ta]') is not null drop table [ta] create table [ta]([taskid] int,[taskname] varchar(4)) insert [ta] select 1,'需求' union all select 2,'设计' union all select 3,'测试' --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([taskid] int,[resourcename] varchar(8)) insert [tb] select 1,'pear' union all select 1,'dong' union all select 2,'peardong' --------------开始查询-------------------------- select a.taskid,a.taskname,b.resourcename from ta a full join (select taskid,[resourcename]=stuff((select '、'+[resourcename] from tb t where taskid=tb.taskid for xml path('')), 1, 1, '') from tb group by taskid )b on a.taskid=b.taskid ----------------结果---------------------------- /*taskid taskname resourcename ----------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 需求 pear、dong 2 设计 peardong 3 测试 NULL(3 行受影响) */
CREATE FUNCTION dbo.f_tb(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + '、' + resourcename FROM tb1 WHERE taskid=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
select tb2.taskid,taskname,resourcename
from tb2 left join (
SELECT taskid, dbo.f_tb(taskid) as resourcename FROM tb1 GROUP BY taskid) t
on tb2.taskid=t.taskid
--2005的方法
select tb2.taskid,taskname,resourcename
from tb2 left join (
select resourcename =stuff((select '|'+resourcename from tb1 t where taskid=tb.taskid for xml path('')), 1, 1, ''),taskid
from tb1
group by taskid ) t on tb2.taskid=t.taskid
第一个表 a right Join 第二个表 b on a.taskid=b.taskid
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 22:14:40
----------------------------------------------------------------
--> 测试数据:[ta]
if object_id('[ta]') is not null drop table [ta]
create table [ta]([taskid] int,[taskname] varchar(4))
insert [ta]
select 1,'需求' union all
select 2,'设计' union all
select 3,'测试'
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([taskid] int,[resourcename] varchar(8))
insert [tb]
select 1,'pear' union all
select 1,'dong' union all
select 2,'peardong'
--------------开始查询--------------------------
select a.taskid,a.taskname,b.resourcename
from ta a full join
(select taskid,[resourcename]=stuff((select '、'+[resourcename] from tb t where taskid=tb.taskid for xml path('')), 1, 1, '')
from tb
group by taskid )b
on
a.taskid=b.taskid
----------------结果----------------------------
/*taskid taskname resourcename
----------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 需求 pear、dong
2 设计 peardong
3 测试 NULL(3 行受影响)
*/