sql 根据表REPLACE字符

[如此]

某表的names列存有这样的数据'1001,1002,1003,1004'
对应着另一张用户表的UserID列
userid username
1001 revan
1002 david
1003 sam
请问如何在查询时将names列中的userid替换为username?
目前想到用REPLACE函数,但是具体使用还存有疑虑

[發糞塗牆]

例子：

if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
insert test 
select 1,'lisa','1001,1002,1003,1004'
go 
  
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
/* 
id          name       key
----------- ---------- --------------------
1           lisa       1001
1           lisa       1002
1           lisa       1003
1           lisa       1004
*/ 

[如此]

引用 1 楼 DBA_Huangzj 的回复:

例子：

if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
insert test 
select 1,'lisa','1001,1002,1003,1004'
go 
  
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
/* 
id          name       key
----------- ---------- --------------------
1           lisa       1001
1           lisa       1002
1           lisa       1003
1           lisa       1004
*/ 

非常感谢您的回答,但是我可能没有表述清楚. 首先是两张表,一个用户表,存有ID,和Name 另一张称作邮件抄送人表吧,存有用逗号分割的用户ID 我想在查询邮件抄送人表时,将用逗号分割的用户ID(ID)替换为用逗号分割的用户名称(Name)

[發糞塗牆]

结果是类似这样？'revan,david..'

[如此]

引用 3 楼 DBA_Huangzj 的回复:

结果是类似这样？'revan,david..'

嗯 是的,就是想要这样的结果. 不好意思啊,我之前没表达清楚.

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-13 13:48:19
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([userid] int,[username] varchar(5))
insert [A]
select 1001,'revan' union all
select 1002,'david' union all
select 1003,'sam'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([names] varchar(19))
insert [B]
select '1001,1002,1003,1004'
--------------开始查询--------------------------




;WITH cte AS (select a.username
from [A] INNER JOIN (
SELECT 
    SUBSTRING([names],number,CHARINDEX(',',[names]+',',number)-number) as [names] 
from
    [B] a,master..spt_values 
where
    number >=1 and number<=len([names])  
    and type='p' 
    and substring(','+[names],number,1)=',')b ON a.userid=b.names)
select DISTINCT
stuff((select ','+username from cte b 
       for xml path('')),1,1,'') 'username'
from cte a
----------------结果----------------------------
/* 
username
---------------------------------------
revan,david,sam

*/

[發糞塗牆]

如果字段名你没给错的话，替换A、B两个表名应该可以了

[LongRui888]

是这样吗：

if OBJECT_ID('[用户表]') is not null
drop table [用户表] 
go 

create table [用户表] 
( 
    userid int, 
    username varchar(10)
) 
go 

insert [用户表] 
select 1001,     'revan'
union all select 1002,'david'
union all select 1003,'sam'
go

if OBJECT_ID('t') is not null
drop table t 
go 

create table t 
( 
    names varchar(30)
) 
go 

insert t 
select '1001,1002,1003,1004'
go 
 
 
;with tt
as
( 
select tt.names,
       u.username
from
( 
select t.names,
       SUBSTRING(t.names, number ,CHARINDEX(',',t.names+',',number)-number) as userid
from t ,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.names,s.number,1) = ','
)tt
inner join 用户表 u
        on u.userid = CAST(tt.userid as int)
)

select distinct 
       names,
       stuff(
              (
                select ','+t2.username
                from tt t2
                where t2.names = t1.names
                for xml path('')
              ),
              1,1,''
            ) as replace_name
from tt t1
/*
names	            replace_name
1001,1002,1003,1004	revan,david,sam
*/

[Leon_He2014]

 select id=1,'1001,1002,1003,1004' names
 into #t
 union all select 2,'1002,1004'

select 1001 userid,'revan' username
into #user
union all select 1002,'david'
union all select 1003,'sam'
union all select 1004,'username'

;with t 
as
(
select id, userid=cast ( SUBSTRING(names+',',1,CHARINDEX(',',names+',')-1) as varchar(max)),splitnames=cast( stuff(names+',',1,CHARINDEX(',',names+','),'') as nvarchar(max))
from #t
union all
select id, userid=cast(SUBSTRING(splitnames,1,CHARINDEX(',',splitnames)-1) as varchar(max)),splitnames=cast(stuff(splitnames,1,CHARINDEX(',',splitnames),'') as nvarchar(max))
from t 
where CHARINDEX(',',splitnames)>0
),t1 as
(
select a.id,b.*
from t a
join #user b
on a.userid=b.userid
)
select  id,names=stuff((select  ','+username from t1 tb where ta.id=tb.id for xml path('')),1,1,'')
from t1 ta
group by id

[如此]

引用 5 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-13 13:48:19
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([userid] int,[username] varchar(5))
insert [A]
select 1001,'revan' union all
select 1002,'david' union all
select 1003,'sam'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([names] varchar(19))
insert [B]
select '1001,1002,1003,1004'
--------------开始查询--------------------------




;WITH cte AS (select a.username
from [A] INNER JOIN (
SELECT 
    SUBSTRING([names],number,CHARINDEX(',',[names]+',',number)-number) as [names] 
from
    [B] a,master..spt_values 
where
    number >=1 and number<=len([names])  
    and type='p' 
    and substring(','+[names],number,1)=',')b ON a.userid=b.names)
select DISTINCT
stuff((select ','+username from cte b 
       for xml path('')),1,1,'') 'username'
from cte a
----------------结果----------------------------
/* 
username
---------------------------------------
revan,david,sam

*/

非常感谢提供的帮助,稍加修改就能使用!

[發糞塗牆]

互相学习，别人的代码也可以