(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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,'' + REPLACE([classid], ',', '') + '') 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,'' + REPLACE([classid], ',', '') + '') 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), '', ''), 1, 1, '')
)N
order by id
drop table tba,tbb
/*
id classname name
----------- -------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(3 行受影响)
*/
update ta set cnpath = path
while exists(select 1 from ta where patindex('%[0-9]%',cnpath)>0)
begin
update a
set a.cnpath = replace(a.cnpath,'|'+ltrim(b.id)+'|','|'+b.name+'|')
from ta a,ta b
where charindex('|'+ltrim(b.id)+'|',a.cnpath) > 0
end
------------------------------------
-- Author: flystone
-- Version:V1.001
-- Date:2009-12-10
------------------------------------
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,name nvarchar(2),path nvarchar(5),prentID nvarchar(1),CNPATH nvarchar(100))
Go
Insert into ta
select 1,'北京','|1|','0','' union all
select 2,'通州','|1|2|','1',''
Go
--Start
update ta set cnpath = path
while exists(select 1 from ta where patindex('%[0-9]%',cnpath)>0)
begin
update a
set a.cnpath = replace(a.cnpath,'|'+ltrim(b.id)+'|','|'+b.name+'|')
from ta a,ta b
where charindex('|'+ltrim(b.id)+'|',a.cnpath) > 0
end
select * from ta
--Result:
/*
ID name path prentID CNPATH
----------- ---- ----- ------- -----------------
1 北京 |1| 0 |北京|
2 通州 |1|2| 1 |北京|通州|
(所影响的行数为 2 行)
*/
--End
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[name] varchar(4),[path] varchar(5),[prentID] int,[CNPATH] varchar(30))
insert [tb]
select 1,'北京','|1|',0,null union all
select 2,'通州','|1|2|',1,null
--select * from [tb]
while EXISTS(select 1 from tb where [CNPATH] is null)
begin
update tb
set [CNPATH] = case [prentID] when 0 then replace([path],ID,name)
else replace(replace([path],ID,name),[prentID],
(select name from tb where id = t.[prentID])) end
from tb t
end
select * from [tb]
--结果
------------------------------
1 北京 |1| 0 |北京|
2 通州 |1|2| 1 |北京|通州|
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-10 21:48:52
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[name] varchar(4),[path] varchar(5),[prentID] int)
insert [tb]
select 1,'北京','|1|',0 union all
select 2,'通州','|1|2|',1
--------------开始查询--------------------------
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+'|'+[name] from tb where charindex('|'+cast(id as varchar)+'|','|'+@id+'|')>0
return stuff(@str,1,1,'')
end
go
select *,CNPATH=dbo.f_hb(path) from tb
drop function f_hb
----------------结果----------------------------
/* ID name path prentID CNPATH
----------- ---- ----- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 北京 |1| 0 北京
2 通州 |1|2| 1 北京|通州
(2 行受影响)
*/
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-12-10 21:40:30
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (ID INT,name VARCHAR(4),path varchar(10),prentID INT,CNPATH varchar(100))
INSERT INTO [tb]
SELECT 1,'北京','|1|',0,null UNION ALL
SELECT 2,'通州','|1|2|',1,null
select * from [tb]
;
with
wang as(select * ,cnpath1=cast('|'+name+'|' as varchar(20)) from tb where prentid=0
union all
select tb.*, cnpath=cast(wang.cnpath1+tb.name+'|' as varchar(20)) from tb join wang on tb.prentid=wang.id
)
update tb
set cnpath=cnpath1
from wang
where tb.id=wang.id
select * from tb
ID name path prentID CNPATH
1 北京 |1| 0 |北京|
2 通州 |1|2| 1 |北京|通州|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10054891/viewspace-622253/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10054891/viewspace-622253/