这个sql 怎么写？

[l244283799]

A表
id sId
1 1,2,3
2 2,3
B表
id name
1 张
2 李
3 王

怎么通过sql 或者存储过程查询出来
id name
1 张,李,王


谢谢！

[oreoconansisu]

if object_id('[A]') is not null drop table [A]
go
create table [A] (id varchar(8),sid varchar(8))
insert into [A] 
select '1','1,2,3' union all
select '2','2,3'

if object_id('[B]') is not null drop table [B]
go
create table [B] (id varchar(8),name nvarchar(8))
insert into [B]
select '1','张' union all
select '2','李' union all
select '3','王'

select id=a.id+','+b.id+','+c.id,name=a.name+','+b.name+','+c.name into #temp20131216
from [B] a 
cross join [B] b 
cross join [B] c

select a.id,b.name
from [a] a
inner join #temp20131216 b on a.sid=b.id

[Andy__Huang]

create table ta(id int,ids varchar(100))
insert into ta
select '1','1,2,3'
union all select '2','2,3'

create table tb(id int,name varchar(10))
insert into tb
select '1','张'
union all select '2','李'
union all select '3','王'
go

create function dbo.fn_name(@ids varchar(10))
returns varchar(1000)
as 
begin
	declare @s varchar(1000)
	set @s=''
	select @s=@s+','+[name] 
	from tb where charindex(','+cast(id as varchar)+',',','+@ids+',')>0
	return (stuff(@s,1,1,''))
end
go


select id,name=dbo.fn_name(ids) from ta
drop table ta,tb
drop function dbo.fn_name


/*
id    name 
----------------------
1	张,李,王
2	李,王

*/

[oreoconansisu]

if object_id('[A]') is not null drop table [A]
go
create table [A] (id varchar(8),sid varchar(8))
insert into [A] 
select '1','1,2,3' union all
select '2','2,3'
 
if object_id('[B]') is not null drop table [B]
go
create table [B] (id varchar(8),name nvarchar(8))
insert into [B]
select '1','张' union all
select '2','李' union all
select '3','王'

select a.id,b.namelist
from [A] a
inner join  
(select a.*,namelist=stuff((
    select  ','+name
	from [B] 
	where 1=1
	for xml path('')),1,1,'')
	from [B] a
)b on a.id=b.id
where len(sid)-len(replace(sid,',',''))=2

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-17 07:32:32
-- 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]([id] int,[sId] varchar(5))
insert [A]
select 1,'1,2,3' union all
select 2,'2,3'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([id] int,[name] varchar(2))
insert [B]
select 1,'张' union all
select 2,'李' union all
select 3,'王'
--------------开始查询--------------------------


;WITH ym AS (
SELECT  a.id,b.name
FROM (
select
    id, 
    SUBSTRING(sid,number,CHARINDEX(',',sid+',',number)-number) as sid 
from
    [A] a,master..spt_values 
where
    number >=1 and number<=len(sid)  
    and type='p' 
    and substring(','+sid,number,1)=',')a INNER JOIN b ON a.sid=b.id)
select a.id,
stuff((select ','+name from ym b 
       where b.id=a.id
       for xml path('')),1,1,'') 'name'
from ym a
group by  a.id

----------------结果----------------------------
/* 
id          name
----------- ------------
1           张,李,王
2           李,王

*/

[t101lian]

引用 楼主 l244283799 的回复:

A表 id sId 1 1,2,3 2 2,3 B表 id name 1 张 2 李 3 王 怎么通过sql 或者存储过程查询出来 id name 1 张,李,王 谢谢！

最简洁的写法

 if object_id('tempdb..#a')is not null
drop table #a
go
create table #a 
(
 id int,
 sid varchar(50)
)
insert into #a
select 1,'1,2,3'  union all
select  2,'2,3'
 
if object_id('tempdb..#b')is not null
drop table #b 
go
create table #b 
(
 id int,
 name varchar(50)
)
insert into #b
select 1,'张' union all
select 2,'李' union all
select 3,'王'  

------查询语句如下 
declare @ids varchar(100)
declare @ss  varchar(2000)
declare @id varchar (2)
set @id=1                --传入进来要查询的条件
select  @ids=sid from #a where id =@id 
set @ss='declare @sql varchar(500) ; select @sql= isnull(@sql+'','','''')+ name from #b where id  in ('+@ids+')'; 
    set @ss =@ss+';select a.id , @sql as name from #a a where id='+@id; 
exec( @ss)

 
 --------结果
1	张,李,王
 

[tenhilltree]

[哥眼神纯洁不]

if exists(select 1 from sys.tables where name='a')
drop table a
create table a(id int,sid varchar(200))
insert into a select 1,'1,2,3' union all select 2,'2,3'
go
if exists(select 1 from sys.tables where name='b')
drop table b
create table b(id int,name varchar(200))
insert into b select 1,'张' union all select 2,'李' union all select 3,'王'
go
if exists(select 1 from sys.procedures where name='proc_a')
drop proc proc_a
go
create proc proc_a
as
begin
declare @a int
declare @b varchar(200)
set @a=1
while @a<=(select MAX(id)+1 from b)
begin
select @b=name from b where id=@a
update a set sid=REPLACE(sid,@a,@b)
set @a=@a+1
end
end
go 
exec proc_a
go
select * from a

[lzw_0736]

--创建数据：
create table #ta(id int,ids varchar(100))
insert into #ta
select 1,'1,2,3'
union all select 2,'2,3'
 
create table #tb(id int,name varchar(10))
insert into #tb
select 1,'张'
union all select 2,'李'
union all select 3,'王'
go

--查询语句：
select id,stuff(
(
select ','+name
from #tb
where charindex(rtrim(id),rtrim(a.ids))>0
for xml path('')
),1,1,'')
from #ta a
where id=1