分解字符串包含的信息值后然后合并到另外一表的信息 (爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响) */
4、把项目串的编码用相应名称代替 ------------------------------------------------------------------------ -- Author: HappyFlyStone -- Date : 2009-01-04 20:57:59 -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation -- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) -- -------------------------------------------------------------------------- Test Data: ta IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta Go CREATE TABLE ta(ID NVARCHAR(3),number varchar(20)) Go INSERT INTO ta SELECT '001','1,2' UNION ALL SELECT '002','1,2,3' GO -- Test Data: tb IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb Go CREATE TABLE tb(ID INT,name NVARCHAR(7)) Go INSERT INTO tb SELECT 1,'测试一' UNION ALL SELECT 2,'测试二' UNION ALL SELECT 3,'测试三' GO --Start --查询一 SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE(( SELECT B.NAME AS NAME FROM TA A LEFT JOIN ( SELECT ID,NAME FROM TB ) B ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0 WHERE A.ID = C.ID FOR XML AUTO ),'"/><B NAME="',','),'"/>',''),1,9,'') FROM TA C --查询二 SELECT A.ID,NUMBER=STUFF( (SELECT ','+NAME FROM TB WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0 FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)') ,1,1,'') FROM TA A--Result: /*id number ---- ----------------- 001 测试一,测试二 002 测试一,测试二,测试三(2 行受影响) */ --End
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 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 行受影响)
*/
------------------------------------------------------------------------
-- Author: HappyFlyStone
-- Date : 2009-01-04 20:57:59
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(ID NVARCHAR(3),number varchar(20))
Go
INSERT INTO ta
SELECT '001','1,2' UNION ALL
SELECT '002','1,2,3'
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(ID INT,name NVARCHAR(7))
Go
INSERT INTO tb
SELECT 1,'测试一' UNION ALL
SELECT 2,'测试二' UNION ALL
SELECT 3,'测试三'
GO
--Start
--查询一
SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE((
SELECT B.NAME AS NAME
FROM TA A
LEFT JOIN
( SELECT ID,NAME
FROM TB
) B
ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0
WHERE A.ID = C.ID
FOR XML AUTO
),'"/><B NAME="',','),'"/>',''),1,9,'')
FROM TA C
--查询二
SELECT A.ID,NUMBER=STUFF(
(SELECT ','+NAME
FROM TB
WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0
FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)')
,1,1,'')
FROM TA A--Result:
/*id number
---- -----------------
001 测试一,测试二
002 测试一,测试二,测试三(2 行受影响)
*/
--End