这个问题有点棘手

Question

我的功能是这样的 。
 一张表是Basic ,里面的字段如下（为了简便，我只列出对我的问题有帮助的字段）
 type  id   basicName
  001   1    小学
  001   2    初中
  001   3    高中另一张school表
  schoolName           schoolType
  希望小学          1,2上面的school的schoolType对应 basic的type为001数据，可以是一个，可以是多个,其中用逗号分隔现在要求查询的结果显示schoolName      schoolType
  希望小学         小学，初中这样的结果不知道怎么用select来实现。。  schoolType是一个，那么很简单select  schoolName     ,  basicName as   schoolType from school ,basic where type='001' and  schoolType=id就可以出来了。 但是现在数据库的关系是以上设计的，所以，大家帮个忙，希望如果有几种答案，给一个性能最佳的，能讲解一下为什么更好，谢谢。。

Answer 1

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

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-22 11:21:43
-------------------------------------
 
--> 生成测试数据: @Base
DECLARE @Base TABLE (type varchar(3),id int,basicName varchar(4))
INSERT INTO @Base
SELECT '001',1,'小学' UNION ALL
SELECT '001',2,'初中' UNION ALL
SELECT '001',3,'高中'
 
--> 生成测试数据: @school
DECLARE @school TABLE (schoolName varchar(8),schoolType varchar(10))
INSERT INTO @school
SELECT '希望小学','1,2'--SQL查询如下:SELECT A.schoolName,STUFF(B.basicName,1,1,'') AS basicName
FROM @school AS A
    OUTER APPLY (
         SELECT basicName = (
                   SELECT ','+basicName AS [text()]
                   FROM @Base
                   WHERE CHARINDEX(','+RTRIM(id)+',',','+A.schoolType+',')>0
                   ORDER BY CHARINDEX(','+RTRIM(id)+',',','+A.schoolType+',')
                   FOR XML PATH(''),TYPE
                ).value('.','varchar(50)')
    
    ) AS B/*
schoolName basicName
希望小学 小学,初中
*/

Answer 3

if object_id('basic')is not null drop table basic
go
create table basic(type varchar(3), id int, basicName VARCHAR(10))
INSERT  basic SELECT
  '001',  1  ,  '小学'  UNION ALL SELECT
  '001' , 2 ,   '初中'  UNION ALL SELECT
  '001'  ,3,    '高中' 
if object_id('school')is not null drop table school
go
create table school(schoolName  varchar(13), schoolType VARCHAR(10))
INSERT  school SELECT
  '希望小学',          '1,2'  go
-----------------------------------------------
create function f_str1(@id varchar(20))
returns varchar(50)
as
begin 
declare @s varchar(20)
select @s=isnull(@s+',','')+basicName
from  
(select schoolName,basicName from basic a,school b
where charindex(','+ltrim(a.id)+',',','+ schoolType+',')>0
)t

return @s
end
go select schoolName,basicName=dbo.f_str1(schoolName)
from schoolschoolName    basicName
------------- --------------------------------------------------
希望小学          小学,初中(1 行受影响)
drop function f_str1

Answer 4

頂5樓！
不過建表的那兩句在我在報錯改為：
create table #@Base (type varchar(3),id int,basicName varchar(4))
create table #@school (schoolName varchar(8),schoolType varchar(10))
其他OK,測試的數據沒問題。
學習了！

Answer 5

create table basic
(
btype nvarchar(120),
basicName varchar(120)
)
create table school 
(
schoolName nvarchar(120),
schoolType nvarchar(120)
)select c.schoolName,d.basicName from school c,
(select btype,left(baName,len(baName)-1) as basicName from (
select a.btype,
baName=(select basicName+',' from basic where a.btype=btype for xml path(''))
from basic a group by a.btype) b) as d where c.schoolType=d.btype