一个关于查询的SQL语句

[guanyelong]

表 course：
id title
1 数学
2 美术
3 体育
4 武术

表 student:
id name courselist
1 张三 1,2
2 李四 3
3 王五 2,3,4
4 赵六 3,4

我想要的查询结果是这样的,请各位大神显神通
id name coursestr
1 张三 数学,美术
2 李四 体育
3 王五 美术,体育,武术
4 赵六 体育,武术

[北冥小渔夫]

CREATE TABLE t_course(id INT,  title VARCHAR(50))
INSERT INTO t_course
SELECT  1 , '数学' UNION ALL 
SELECT  2 ,'美术' UNION ALL 
SELECT  3 ,'体育' UNION ALL 
SELECT  4, '武术'

CREATE TABLE t_student(id INT , name VARCHAR(50), courselist VARCHAR(50) )
INSERT INTO t_student
SELECT 1, '张三',     '1,2' UNION ALL 
SELECT 2, '李四',      '3'  UNION ALL 
SELECT 3, '王五',     '2,3,4' UNION ALL 
SELECT 4, '赵六',     '3,4'

SELECT 
       t1.id,  t1.name,
       courselist = SUBSTRING(t1.courselist, t2.number, CHARINDEX(',', t1.courselist + ',', t2.number) -t2.number )
INTO #T1
FROM   t_student t1,  master..spt_values  t2
WHERE  t2.type = 'P' AND CHARINDEX(',', ',' + t1.courselist, t2.number) = t2.number
GROUP BY  t1.id,  t1.name, t1.courselist,
       SUBSTRING(  t1.courselist, t2.number,CHARINDEX(',', t1.courselist + ',', t2.number)-t2.number )

SELECT t.*,tc.title
INTO #T2
FROM #T1 t
LEFT JOIN t_course tc ON t.courselist = tc.id

SELECT
id,name,
coursestr = (SELECT STUFF((SELECT ','+title FROM #T2 WHERE id = t2.id FOR XML PATH('')),1,1,''))
FROM #T2 t2
GROUP BY id ,name 

DROP TABLE #T1
DROP TABLE #T2


id          name                 coursestr
----------- -------------------- --------------
1           张三                   数学,美术
2           李四                   体育
3           王五                   美术,体育,武术
4           赵六                   体育,武术

(4 行受影响)

[雨夹雪]

CREATE TABLE #student 
(
id INT, 
name VARCHAR(10),  
courselist VARCHAR(100)
)

INSERT INTO #student VALUES(1 ,'张三',     '1,2')
INSERT INTO #student VALUES(2 ,'李四',      '3')
INSERT INTO #student VALUES(3 ,'王五',     '2,3,4')
INSERT INTO #student VALUES(4 ,'赵六',     '3,4')


SELECT id,name,STUFF((SELECT ','+title  FROM #course WHERE CHARINDEX(CONVERT(VARCHAR(10),id)+',',a.courselist+',') >0 FOR XML PATH('') ),1,1,'') AS  courselist
FROM #student a