一列中的多关联

[wysfair]

表 tb1
courseId
---------------
2,3
1,2
2
1,2,3
....

表 tb2
courseId courseName
----------------------
1 语文
2 数学
3 英语
....

现对tb1与tb2进行联表查询 得出
Course
---------
数学,英语
语文,数学
数字
语文,数学,英语
.....


有大大帮忙没?在此先谢过

[hch126163]

1楼 牛人！

[--小F--]

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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 行受影响)
*/

[lymmingv]

学习。。。。。。。。。。

[SQLCenter]

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(courseId varchar(8))
insert into #1
select '2,3' union all
select '1,2' union all
select '2' union all
select '1,2,3'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(courseId int, courseName varchar(8))
insert into #2
select 1, '语文' union all
select 2, '数学' union all
select 3, '英语'

;with cte as
(
	select a.courseId, b.courseName from #1 a, #2 b where ','+a.courseId+',' like '%,'+ltrim(b.courseId)+',%'
)
select courseId, Course=stuff((select ','+courseName from cte where courseId=t.courseId for xml path('')),1,1,'') from cte t group by courseId

/*
courseId Course
-------- --------------
1,2      语文,数学
1,2,3    语文,数学,英语
2        数学
2,3      数学,英语
*/

[wysfair]

楼上的大大..能给我讲一下原理不?

[SQLCenter]

自己拆解一下不就明白了

;with cte as
(
    select a.courseId, b.courseName from #1 a, #2 b where ','+a.courseId+',' like '%,'+ltrim(b.courseId)+',%'
)
select ','+courseName from cte where courseId='1,2' for xml path('')
/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------
,语文,数学
*/

[天下如山]

学习啦 我做起来就麻烦多啦。

[wysfair]

不好意思...2L的...你的方法很好...
但有问题

当我的表 tb1有两条一样的记录时..结果就会不正确
courseId
---------------
2,3
1,2
2
2,3
1,2
1,2,3

看看有什么改良的方法