表tba中
localnm quatity matnm
xxx1,xx2,xx3 1 材料１,材料3
yyy 2 设置１,设置2
cc1,cc8 8 测试
nn9,nn0,nn3 5 ii,iii
表tbb中
localnm quatity matnm
xx3,xx2, xx1 1 材料１,材料3
yyy 1 设置１,设置2
cc1,cc8,cc 8 测试
nn3,nn9,nn0 5 ii
两个表中localnm都是以逗号隔开，matnm也是以逗号隔开，
如果matnm中第一个相等时就对比，如果quatity不相等时就有问题，
或者localnm不相等时就有问题，localnm中的不相等不是看全个字符串一样不的，
而是看分开逗号后tba能不能在tbb中找到,
如tba第一行matnm第一个都是材料１,tbb 也是材料1，所以这两行对比
tba中的xx1能在tbb中找到xx1,虽然tbb中xx1前有空隔,xx2和xx3也能找到，
第四行也是能完全找到
所以想得到
localnm quatity matnm flag
xxx1,xx2,xx3 1 材料１,材料3 Y
nn9,nn0,nn3 5 ii,iii Y
而第三行tbb的localnm多了cc
所以
localnm quatity matnm flag
cc1,cc8 8 测试 N
第二行中tba和tbb中的quatity不一样，所以最后得到
localnm quatity matnm flag
yyy 2 设置１,设置2 N
要怎么写语句，最后得到各个结果
localnm quatity matnm flag
------解决方案--------------------
按照如下的方法拆分后再去对比,至于如何对比,你就自己琢磨吧.
- SQL code
/*标题：简单数据拆分(version 2.0)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2010-05-07地点：重庆航天职业学院描述：有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc*/--1. 旧的解决方法(sql server 2000)create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')go--方法1.使用临时表完成SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--方法2.如果数据量小，可不使用临时表select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) from tb a join master..spt_values b on b.type='p' and b.number between 1 and len(a.value)where substring(',' + a.value , b.number , 1) = ','--2. 新的解决方法(sql server 2005)create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')go--方法1.使用xml完成SELECT A.id, B.value FROM( SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb) A OUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)) B--方法2.使用CTE完成;with tt as (select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tbunion allselect id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>'')select id,[value] from tt order by id option (MAXRECURSION 0)DROP TABLE tb/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc(5 行受影响)*/
------解决方案--------------------
- SQL code
SQL2005下利用XML进行项目的合并与拆分 通常情况下我们对相同项目进分组求和，那是相当的简单啦，只要select.. group by加聚合函数就行了，可是对于串一类的项目进行合并时就不这么简单了，同样分解一个按指定分隔符分隔的串或分析指定位置的串，在2000下我们通常是创建一个函数，然后分组或提取就行了，现我们主要讨论在2005下利用xml来完成这个工作。 1、先来一个简单点，如下的例子对aaa相同的项目合并。-------------------------------------- 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: tbIF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbGoCREATE TABLE tb(aaa INT,bbb INT)GoINSERT INTO tbSELECT 1,2 UNION ALLSELECT 1,3 UNION ALLSELECT 1,4 UNION ALLSELECT 2,2 UNION ALLSELECT 2,5 GO--查询1select aaa, [values]=stuff(replace(replace((select [bbb] from tb where aaa=t.aaa for xml AUTO), '"/><tb bbb="',','), '"/>',''),1,9,'')from tb tgroup by aaa --查询2SELECT * FROM( SELECT DISTINCT aaa FROM tb )A OUTER APPLY( SELECT [bbb]= STUFF(REPLACE(REPLACE( ( SELECT [bbb] FROM tb N WHERE aaa = A.aaa FOR XML AUTO ), '<N bbb="', ','), '"/>', ''), 1, 1, '') )N --查询3select aaa, [values]=stuff((select ','+ltrim([bbb]) from tb t where aaa=tb.aaa for xml path('')), 1, 1, '') from tb group by aaa drop table tb--查询结果/*aaa values----------- ---------1 2,3,42 2,5(2 行受影响)*/2、来个两个表关联操作并实现行列转换的-------------------------------------- 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: taIF OBJECT_ID('ta') IS NOT NULL DROP TABLE taGoCREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))GoINSERT INTO taSELECT 1,1,'hy3500' UNION ALLSELECT 1,2,'aabbcc' UNION ALLSELECT 2,3,'1111' UNION ALLSELECT 2,4,'2222' GO-- Test Data: tbIF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbGoCREATE TABLE tb(NAME NVARCHAR(2),id INT)GoINSERT INTO tb SELECT '型号',1 UNION ALL SELECT '参数',2 GO--StartSELECT t.[name],A,BFROM( SELECT B.[NAME], CAST((SELECT [name] FROM TA WHERE PID = A.PID FOR XML PATH('')) AS XML) AS X FROM TA A LEFT JOIN TB B ON A.PID = B.ID GROUP BY B.[NAME],A.PID) tCROSS APPLY (SELECT A=t.x.value('/name[1]','VARCHAR(10)'),B = t.x.value('/name[2]','VARCHAR(10)')) M--Result:/*c a b---- ---------- ----------参数 1111 2222型号 hy3500 aabbcc(2 行受影响)*/--End 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)-- ------------------------------------DECLARE @t TABLE(c VARCHAR(20))INSERT @t SELECT '双桥,9.6米,30.0吨'UNION ALL SELECT 'aa,bb,cc,dd'--通常情况如果项目在四个项目以内时，推荐一种方法：SELECT REPLACE(PARSENAME(XX,3),'$$','.') C , REPLACE(PARSENAME(XX,2),'$$','.') B FROM( SELECT REPLACE(REPLACE(c,'.','$$'),',','.') XX FROM @T)AA--result/*c b --------------------------------- -----------双桥 9.6米（所影响的行数为 1 行）*/好，那我们来看看2005下XML如何处理的SELECT A,B FROM (SELECT CAST('<r>' + REPLACE(c,',','</r><r>') + '</r>' AS XML) x,c FROM @t) aCROSS APPLY (SELECT A=a.x.value('/r[1]','VARCHAR(10)'),B=a.x.value('/r[2]','VARCHAR(10)')) b/*A B---------- ----------双桥 9.6米aa bb(2 行受影响)*/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: taIF OBJECT_ID('ta') IS NOT NULL DROP TABLE taGoCREATE TABLE ta(ID NVARCHAR(3),number varchar(20))GoINSERT INTO ta SELECT '001','1,2' UNION ALL SELECT '002','1,2,3' GO-- Test Data: tbIF OBJECT_ID('tb') IS NOT NULL DROP TABLE tbGoCREATE TABLE tb(ID INT,name NVARCHAR(7))GoINSERT INTO tbSELECT 1,'测试一' UNION ALLSELECT 2,'测试二' UNION ALLSELECT 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 5、最后我们来说说折分吧如：/*id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc*/一般我们在2000下会借助中间生成一个连续的序列，然后和表关联折分，在2005下我们可借助CTE生成一个序列然后再拆分 CREATE TABLE TB(ID INT,VALUE VARCHAR(30))INSERT INTO TB VALUES(1,'AA,BB')INSERT INTO TB VALUES(2,'AAA,BBB,CCC')GOIF OBJECT_ID('TEMPDB..#NUM') IS NOT NULL DROP TABLE #NUMGO--2000SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM FROM SYS.SYSCOLUMNS A,SYS.SYSCOLUMNS BSELECT A.ID,[VALUE]=SUBSTRING(A.[VALUE],B.ID,CHARINDEX(',',A.[VALUE]+',',B.ID)-B.ID) FROM TB A,#NUM BWHERE CHARINDEX(',',','+A.[VALUE],B.ID)=B.ID --也可用 SUBSTRING(','+A.COL2,B.ID,1)=','--2005;WITH TAS( SELECT 1 AS ID UNION ALL SELECT ID + 1 FROM T WHERE ID < 100)SELECT A.ID,[VALUE]=SUBSTRING(A.[VALUE],B.ID,CHARINDEX(',',A.[VALUE]+',',B.ID)-B.ID) FROM TB A,T BWHERE CHARINDEX(',',','+A.[VALUE],B.ID)=B.ID --也可用 SUBSTRING(','+A.COL2,B.ID,1)=','DROP TABLE TB,#NUM-------------------------------------- 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)-- ------------------------------------CREATE TABLE TB(ID INT,VALUE VARCHAR(30))INSERT INTO TB VALUES(1,'AA,BB')INSERT INTO TB VALUES(2,'AAA,BBB,CCC')GOSELECT A.ID, B.VALUEFROM( SELECT ID, [value] = CONVERT(XML,'<ROOT><V>' + REPLACE([VALUE], ',', '</V><V>') + '</V></ROOT>') FROM tb)AOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/ROOT/V') N(v))BDROP TABLE tb--查询结果/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc(5 行受影响)*/