a表：
proname, begindate, enddate, workers
project1 2008-10-1 2008-10-12 1,3,4,5
project2 2008-11-1 2008-12-1 4,7,1
project3 2008-10-17 2008-10-19 1
project4 2009-1-1 2009-1-12 1,3,7,5b表：
username userid
jack 1
linda 3
rose 4
peter 5
susan 7b表是a表中workers的对照表要达到这样的效果：
proname, begindate, enddate, workers
project1 2008-10-1 2008-10-12 jack,linda,rose,peter
project2 2008-11-1 2008-12-1 rose,susan,jack
project3 2008-10-17 2008-10-19 jack
project4 2009-1-1 2009-1-12 jack,linda,susan,peter
proname, begindate, enddate, workers
project1 2008-10-1 2008-10-12 1,3,4,5
project2 2008-11-1 2008-12-1 4,7,1
project3 2008-10-17 2008-10-19 1
project4 2009-1-1 2009-1-12 1,3,7,5b表：
username userid
jack 1
linda 3
rose 4
peter 5
susan 7b表是a表中workers的对照表要达到这样的效果：
proname, begindate, enddate, workers
project1 2008-10-1 2008-10-12 jack,linda,rose,peter
project2 2008-11-1 2008-12-1 rose,susan,jack
project3 2008-10-17 2008-10-19 jack
project4 2009-1-1 2009-1-12 jack,linda,susan,peter
if object_id('ta')is not null drop table ta
go
create table ta(proname varchar(10), begindate datetime, enddate datetime, workers varchar(10))
insert ta select 'project1', '2008-10-1', '2008-10-12', '1,3,4,5'
insert ta select 'project2', '2008-11-1' , '2008-12-1' , '4,7,1'
insert ta select 'project3', '2008-10-17', '2008-10-19', '1'
insert ta select 'project4', '2009-1-1' , '2009-1-12' , '1,3,7,5'
if object_id('tb')is not null drop table tb
go
create table tb(username varchar(10), userid int)
insert tb select 'jack' , 1
insert tb select 'linda' , 3
insert tb select 'rose' , 4
insert tb select 'peter' , 5
insert tb select 'susan', 7
if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns varchar(50)
as
begin
declare @temp table(col varchar(10))
declare @i int
declare @s varchar(100)
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
select @s=isnull(@s+',','')+b.username from tb b ,@temp t where b.userid=t.col
return @s
end
go select proname ,begindate , enddate,dbo.fn_split(workers,',') from ta a
/*proname begindate enddate
---------- ------------------------------------------------------ ------------------------------------------------------ --------------------------------------------------
project1 2008-10-01 00:00:00.000 2008-10-12 00:00:00.000 jack,linda,rose,peter
project2 2008-11-01 00:00:00.000 2008-12-01 00:00:00.000 jack,rose,susan
project3 2008-10-17 00:00:00.000 2008-10-19 00:00:00.000 jack
project4 2009-01-01 00:00:00.000 2009-01-12 00:00:00.000 jack,linda,peter,susan*/
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
create table [A]([proname] varchar(8),[begindate] datetime,[enddate] datetime,[workers] varchar(7))
insert [A]
select 'project1','2008-10-1','2008-10-12','1,3,4,5' union all
select 'project2','2008-11-1','2008-12-1','4,7,1' union all
select 'project3','2008-10-17','2008-10-19','1' union all
select 'project4','2009-1-1','2009-1-12','1,3,7,5'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
create table [B]([username] varchar(16),[userid] int)
insert [B]
select 'jack',1 union all
select 'linda',3 union all
select 'rose',4 union all
select 'peter',5 union all
select 'susan',7GO
create function fn1(@ids varchar(32))
returns varchar(64) as
begin
declare @names varchar(64)
select @names=isnull(@names+',','')+[username]
from B where charindex(','+rtrim([userid])+',', ','+@ids+',')>0
--order by charindex(','+rtrim([userid])+',', ','+@ids+',')
return @names
end
goselect proname, begindate, enddate, workers=dbo.fn1(workers) from [A]
/*
proname begindate enddate workers
-------- ----------------------- ----------------------- --------------------------
project1 2008-10-01 00:00:00.000 2008-10-12 00:00:00.000 jack,linda,rose,peter
project2 2008-11-01 00:00:00.000 2008-12-01 00:00:00.000 jack,rose,susan
project3 2008-10-17 00:00:00.000 2008-10-19 00:00:00.000 jack
project4 2009-01-01 00:00:00.000 2009-01-12 00:00:00.000 jack,linda,peter,susan(4 行受影响)
*/drop table A,B
drop function dbo.fn1
--> --> (让你望见影子的墙)生成測試數據
if not object_id('tba') is null
drop table tba
Go
Create table tba([proname,] nvarchar(8),[begindate,] Datetime,[enddate,] Datetime,[workers] nvarchar(100))
Insert tba
select N'project1','2008-10-1','2008-10-12',N'1,3,4,5' union all
select N'project2','2008-11-1','2008-12-1',N'4,7,1' union all
select N'project3','2008-10-17','2008-10-19',N'1' union all
select N'project4','2009-1-1','2009-1-12',N'1,3,7,5'
Go
Select * from tba
--> --> (让你望见影子的墙)生成測試數據
if not object_id('tbb') is null
drop table tbb
Go
Create table tbb([username] nvarchar(5),[userid] varchar)
Insert tbb
select N'jack',1 union all
select N'linda',3 union all
select N'rose',4 union all
select N'peter',5 union all
select N'susan',7
Go
Select * from tbbwhile @@rowcount>0
beginupdate t
set workers=stuff(workers,patindex('%[1-9]%',workers),1,(select username from tbb where userid=substring(t.workers,patindex('%[1-9]%',t.workers),1)))
from tba t,tbb
where charindex(userid,workers)>0end
select * from tbaproject1 2008-10-01 00:00:00.000 2008-10-12 00:00:00.000 jack,linda,rose,peter
project2 2008-11-01 00:00:00.000 2008-12-01 00:00:00.000 rose,susan,jack
project3 2008-10-17 00:00:00.000 2008-10-19 00:00:00.000 jack
project4 2009-01-01 00:00:00.000 2009-01-12 00:00:00.000 jack,linda,susan,peter