这个该怎么写呢

[xinyi124]

A表 X字段内容 3,5,7
B表 Num Name
1 abc
2 abc
3 abc
4 abc
5 abc
6 abc
7 abc
8 abc

inner join 查询时候 能获取到 3,5,7对应的Name值

或者要怎么解决。。先谢谢了

[唐诗三百首]

select b.Num,b.Name
from A表 a
inner join B表 b
on a.X=b.Num

[xinyi124]

我希望得到的结果是 abc,abc,abc..

[xinyi124]

谁来帮帮忙。。。。

[Felixzhaowenzhong]

写个表值函数，如何和 B表做 管理即可

[xinyi124]

要怎么写额 - -

[开着拖拉机泡妞]

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)

/*问题描述
tba
ID  classid   name
1     1,2,3   西服 
2     2,3     中山装
3     1,3     名裤
tbb 
id   classname
1     衣服
2     上衣
3     裤子

我得的结果是
id   classname            name
1     衣服,上衣,裤子      西服 
2     上衣，裤子          中山装
3     衣服，裤子          名裤
*/

-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go

--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go 
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id          classid       name       
----------- ------------- ---------- 
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
（所影响的行数为 3 行）
*/

--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID          classid              name       
----------- -------------------- ---------- 
1           衣服,上衣,裤子       西服
2           上衣,裤子            中山装
3           衣服,裤子            名裤
（所影响的行数为 3 行）
*/
drop table tba,tbb

------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go

SELECT id , classname , name FROM
(
  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 
  (
    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
  ) tbc , tbb where tbc.classid = tbb.id
  ) T
)A 
OUTER APPLY
(
  SELECT [classname]= STUFF(REPLACE(REPLACE((
    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 
    (
      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
    ) tbc , tbb where tbc.classid = tbb.id
  ) N
  WHERE id = A.id and name = A.name
  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by id

drop table tba,tbb

/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/


自己修改一下吧

唐诗哥哥回家吃饭去了

[开着拖拉机泡妞]

go
if object_id('[A1]') is not null 
drop table [A1]
go
create table [A1](
[ID] varchar(1),
[X] varchar(5)
)
go
insert [A1]
select '1','3,5,7'
--> 测试数据：[B2]
go
if object_id('[B2]') is not null 
drop table [B2]
go
create table [B2](
[Num] varchar(2),
[Name] varchar(3)
)
go
insert [B2]
select '1','abc' union all
select '2','abc' union all
select '3','abc' union all
select '4','abc' union all
select '5','abc' union all
select '6','abc' union all
select '7','abc' union all
select '8','abc'

go
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[Name] from [B2] 
  where charindex(','+cast([Num] as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end


go 
select classid=dbo.f_hb([X]) from [A1]

/*
classid
abc,abc,abc
*/

[Felixzhaowenzhong]

CREATE FUNCTION [dbo].[fn_get_Split]
(
@SplitString varchar(4000),
@Separator varchar(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000)
)
AS
BEGIN
DECLARE @CurrentIndex int
DECLARE @NextIndex int
DECLARE @ReturnText varchar(8000)
SELECT @CurrentIndex=1
WHILE(@CurrentIndex<=datalength(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex)
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=datalength(@SplitString)+1

SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex)

INSERT INTO @SplitStringsTable([value])
VALUES(@ReturnText)

SELECT @CurrentIndex=@NextIndex+1
END
RETURN
END


----
declare @str varchar(32)
set @str= '3,5,7'
/*如果取多条的话，用循环从你的A表中取值传给参数调用 表值函数就可以了*/


create table tabs (num int,name varchar(32))
insert into tabs select 1,'abc' union all
select 2,'abc'union all
select 3,'abc'union all
select 4,'abc'union all
select 5,'abc'union all
select 6 ,'abc'union all
select 7,'abc'union all
select 8,'abc'


select b.* from tabs as b,(select * from fn_get_Split(@str,',')) as a where b.num=a.value
/*
num name
3 abc
5 abc
7 abc
*/


--drop table tabs
--drop function fn_get_Split

[xinyi124]

感谢各路大神。。受教了

[Mr_Nice]

if object_id('[TB_B]') is not null drop table [TB_B]
go
create table [TB_B] (Num int,Name nvarchar(6))
insert into [TB_B]
select 1,'abc' union all
select 2,'abc' union all
select 3,'abc' union all
select 4,'abc' union all
select 5,'abc' union all
select 6,'abc' union all
select 7,'abc' union all
select 8,'abc'

select * from [TB_B]


declare @i varchar(30)
set @i='3,5,7'

exec('select * from TB_B where Num like ''['+@i+']'+'''')

如果仅仅是单独的数值的话.