主键拼接成外键的链表查询

sjylove 2010-03-11 01:22:15





主表department:



Id     Name  

1      中国

2      上海

3      浦东





从表：listPrice



Id    Name         DepartmentId

1      价格1        1，2

2      价格2        2，3

3      价格3        1，2，3





要求结果：（一条SQL语句）



Id          Name            DepartmentId              DepartmentName

1           价格1            1，2                       中国，上海

2           价格2            2，3                       上海，浦东

3           价格3            1，2，3                    中国，上海，浦东





请各位高手帮忙！

...全文

168 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

zhuangxiuming1 2010-03-11

打赏
举报

哇，好漂亮，好强大啊！！！

sql_sf 2010-03-11

打赏
举报

[Quote=引用 12 楼 sjylove 的回复:]
引用 11 楼 sql_sf 的回复:
2005不要搞那麼複雜

like '%,'+ltrim(ID)+',%' 这里的ID是哪个表的ID？ listprice么？
[/Quote]
department.ID

sjylove 2010-03-11

打赏
举报

[Quote=引用 11 楼 sql_sf 的回复:]
2005不要搞那麼複雜
[/Quote]

like '%,'+ltrim(ID)+',%' 这里的ID是哪个表的ID？ listprice么？

sql_sf 2010-03-11

打赏
举报

2005不要搞那麼複雜

sql_sf 2010-03-11

打赏
举报

還是寫吧

if not object_id('department') is null

	drop table department

Go

Create table department([Id] int,[Name] nvarchar(2))

Insert department

select 1,N'中国' union all

select 2,N'上海' union all

select 3,N'浦东'

Go

if not object_id('listPrice') is null

	drop table listPrice

Go

Create table listPrice([Id] int,[Name] nvarchar(3),[DepartmentId] nvarchar(5))

Insert listPrice

select 1,N'价格1',N'1,2' union all

select 2,N'价格2',N'2,3' union all

select 3,N'价格3',N'1,2,3'

Go

Select a.*,

      DepartmentName2

      =stuff((select ','+[Name] 

              from  department  

              where ','+[DepartmentId]+',' like '%,'+ltrim(ID)+',%' for xml path('')),1,1,'') 

from listPrice a

/*

Id          Name DepartmentId DepartmentName2

----------- ---- ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           价格1  1,2          中国,上海

2           价格2  2,3          上海,浦东

3           价格3  1,2,3        中国,上海,浦东



(3 個資料列受到影響)



*/

sjylove 2010-03-11

打赏
举报

[Quote=引用 8 楼 fredrickhu 的回复:]
那就写函数吧哈哈不然就2005 先分拆再合并
[/Quote]

对于你提供的那个函数，我没打算研究，超出了我的水平太多了。。。

--小F-- 2010-03-11

打赏
举报

那就写函数吧哈哈不然就2005 先分拆再合并

sjylove 2010-03-11

打赏
举报

[Quote=引用 6 楼 fredrickhu 的回复:]
引用 4 楼 sjylove 的回复:
引用 3 楼 fredrickhu 的回复:
顺便葱白一下强大的乌龟

有简单点的实现没，直接看不懂

基本所有的方法都在这里了其中第2种方法比较简单一点
[/Quote]

你第2种方法更新物理表了啊。

--小F-- 2010-03-11

打赏
举报

[Quote=引用 4 楼 sjylove 的回复:]
引用 3 楼 fredrickhu 的回复:
顺便葱白一下强大的乌龟

有简单点的实现没，直接看不懂
[/Quote]

基本所有的方法都在这里了其中第2种方法比较简单一点

sjylove 2010-03-11

打赏
举报

各路高手，快快支招啊！

sjylove 2010-03-11

打赏
举报

[Quote=引用 3 楼 fredrickhu 的回复:]
顺便葱白一下强大的乌龟
[/Quote]

有简单点的实现没，直接看不懂

--小F-- 2010-03-11

打赏
举报

顺便葱白一下强大的乌龟

--小F-- 2010-03-11

打赏
举报

分解字符串包含的信息值后然后合并到另外一表的信息

(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  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 行受影响)

*/