开发学院数据库MSSQL Server SQL2005下利用XML进行项目的合并与拆分 阅读

SQL2005下利用XML进行项目的合并与拆分

 2010-04-20 00:00:00 来源:WEB开发网   
核心提示: 把项目串的编码用相应名称代替--Author:HappyFlyStone--Date:2009-01-0420:57:59--Ver:MicrosoftSQLServer2005-9.00.2047.00(IntelX86)--Apr14200601:12:25--Copyright(c)198

把项目串的编码用相应名称代替

------------------------------------------------------------------------ 
-- 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.(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

上一页  1 2 3 4 5  下一页

Tags:SQL 利用 XML

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接