我有两张表：一张是申请表，一张是申请理由表
申请表：userid， applyID
1 1
2 2
3 1,2,3
理由表：applyID， applyName
1 吃饭
2 睡觉
3 做梦
我现在想要一个视图，需要的是将两张表连接起来，比如出现的是
userid applyName
1 吃饭
2 睡觉
3 吃饭，睡觉，做梦
请问这个sql语句应该怎么写？
或者不用sql，在程序里解决？我是用aps.net（C#）
------解决方案--------------------
- SQL code
--> --> (Roy)生成測試數據 if not object_id('申请表') is null drop table 申请表GoCreate table 申请表([userid] int,[applyID] nvarchar(5))Insert 申请表select 1,N'1' union allselect 2,N'2' union allselect 3,N'1,2,3'Go--> --> (Roy)生成測試數據 if not object_id('理由表') is null drop table 理由表GoCreate table 理由表([applyID] int,[applyName] nvarchar(2))Insert 理由表select 1,N'吃饭' union allselect 2,N'睡觉' union allselect 3,N'做梦'GoCreate view v_申请表asselect userid,[applyName]=stuff((Select ','+[applyName] from 理由表 where ','+a.[applyID]+',' like '%,'+RTRIM([applyID])+',%' for xml path('')),1,1,'')from 申请表 as ago--或用 patindex/charindexGoCreate view v_申请表2asselect userid,[applyName]=stuff((Select ','+[applyName] from 理由表 where patindex('%,'+RTRIM([applyID])+',%',','+a.[applyID]+',' )>0 for xml path('')),1,1,'')from 申请表 as agoselect * from v_申请表2select * from v_申请表/*userid applyName1 吃饭2 睡觉3 吃饭,睡觉,做梦*/
------解决方案--------------------
- SQL code
---还有三种方法：：分解字符串包含的信息值后然后合并到另外一表的信息(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-23 广东深圳)/*问题描述tbaID classid name1 1,2,3 西服 2 2,3 中山装3 1,3 名裤tbb id classname1 衣服2 上衣3 裤子我得的结果是id classname name1 衣服,上衣,裤子 西服 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)asbegin declare @str varchar(1000) set @str='' select @[email protected]+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',',[email protected]+',')>0 return stuff(@str,1,1,'')endgo select id,classid=dbo.f_hb(classid),name from tbadrop function f_hb/*id classid name ----------- ------------- ---------- 1 衣服,上衣,裤子 西服2 上衣,裤子 中山装3 衣服,裤子 名裤（所影响的行数为 3 行）*/--第2种方法.updatewhile(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))update tbaset classid= replace(classid,tbb.id,tbb.classname)from tbbwhere charindex(tbb.id,tba.classid)>0select * from tba/*ID classid name ----------- -------------------- ---------- 1 衣服,上衣,裤子 西服2 上衣,裤子 中山装3 衣服,裤子 名裤（所影响的行数为 3 行）*/drop table tba,tbb--------------------------------------sql server 2005中先分解tba中的classid,然后再合并classnamecreate 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, ''))Norder by iddrop table tba,tbb/*id classname name----------- -------------- ----------1 衣服,上衣,裤子 西服2 上衣,裤子 中山装3 衣服,裤子 名裤(3 行受影响)*/