2个表合一个表sql如何写啊！

Question

有2个表a 和 b， a里字段a1保存b表中id信息
a1
10，11，12b表id如下
bid sm
10 男
11 女
12 童想输出a表全部内容且 a表中的a1字段 变成 b表中的sm不知道说清楚没，我再举个例子
比如
a表有3条数据
001，测试1，10#11#12
002，测试2，10#11
003，测试3，b表数据
10，男人
11，女人
12，儿童现在要显示下面结果001，测试1，10#11#12 ，男人女人 儿童
002，测试2，10#11    ，男人女人
003，测试3，         ，最好只用一条sql语句写
 

Answer 1

http://topic.csdn.net/u/20100311/13/9bb4ed06-a31e-4997-a049-f33e5f79d525.html

Answer 2

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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','裤子')
goSELECT 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 iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

Answer 3

--寫个2005的吧
if not object_id('ta') is null
drop table ta
Go
Create table ta([a] nvarchar(3),[b] nvarchar(3),[c] nvarchar(8))
Insert ta
select N'001',N'测试1',N'10#11#12' union all
select N'002',N'测试2',N'10#11' union all
select N'003',N'测试3',null
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([a] int,[b] nvarchar(2))
Insert tb
select 10,N'男人' union all
select 11,N'女人' union all
select 12,N'儿童'
Go
select a.*,
       [list]=(select ' '+[b] 
               from tb b
               where '#'+a.[c]+'#' like '%#'+ltrim(b.[a])+'#%' for xml path(''))
from ta a
/*
a    b    c        list
---- ---- -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001  测试1  10#11#12  男人 女人 儿童
002  测试2  10#11     男人 女人
003  测试3  NULL     NULL(3 個資料列受到影響)
*/

Answer 4

 
--drop table  A,Bcreate table A(a1 varchar(10),a2 varchar(10),a3 varchar(20))
insert into A values('001','测试1','10#11#12')
insert into A values('002','测试2','10#11')
insert into A values('003','测试3',null)
go
Create Table B(B varchar(10),B2 varchar(10))
insert into B values('10','男人')
insert into B values('11','女人')
insert into B values('12','儿童')select *, 
(select ',' +B.B2 
from A join B on charindex('#'+B.B+'#','#'+A.A3+'#')>0 where A.a1=t.a1 
for xml path('') ) as nn
from A ta1         a2         a3                   nn
---------- ---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001        测试1        10#11#12             ,男人,女人,儿童
002        测试2        10#11                ,男人,女人
003        测试3        NULL                 NULL(3 行受影响)

Answer 5

写个函数
Create table ta([a] nvarchar(3),[b] nvarchar(3),[c] nvarchar(8))
Insert ta
select N'001',N'测试1',N'10#11#12' union all
select N'002',N'测试2',N'10#11' union all
select N'003',N'测试3',null
GoCreate table tb([a] int,[b] nvarchar(2))
Insert tb
select 10,N'男人' union all
select 11,N'女人' union all
select 12,N'儿童'
Gocreate FUNCTION [dbo].[f_str](@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(1000)
    SET @re=''
    SELECT @re=@re+'#'+[b]
    FROM tb
    WHERE  '#'+@col1+'#' like '%#'+cast(a as varchar)+'#%'
    RETURN(STUFF(@re,1,1,''))
ENDselect a.a,a.b,dbo.f_str(c) as c
       
from ta a
a    b    c
---- ---- --------------------
001  测试1  男人#女人#儿童
002  测试2  男人#女人
003  测试3  NULL(3 行受影响)

Answer 6

补充一下
create FUNCTION [dbo].[f_str](@col1 varchar(200))把长度定义的长点。

Answer 7

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 tbaup............