两个表的对表

Question

表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
  

Answer 1

晕啊，怎么出来是这样，字段间都分得很开的，出来怎么挤在一起的
localnm                     quatity                       matnm
 xxx1,xx2,xx3                 1                            材料１,材料3

Answer 2

localnm=xxx1,xx2,xx3 
quatity=1
matnm=材料１,材料3

Answer 3

按照如下的方法拆分后再去对比,至于如何对比,你就自己琢磨吧.
/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           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, # B
WHERE 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 tb
union all
select 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           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 4

--方法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  是怎么个用法？  不晓得

Answer 5

--方法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  是怎么个用法？  不晓得

Answer 6

感觉在sql中实现比较复杂，最后在代码中去对比了

Answer 7

/*
sql xml 入门:
    --by jinjazz
    --http://blog.csdn.net/jinjazz
    
    1、xml:        能认识元素、属性和值
    
    2、xpath:    寻址语言，类似windows目录的查找(没用过dir命令的话就去面壁)
                
                语法格式，这些语法可以组合为条件：
                "."表示自己，".."表示父亲，"/"表示儿子，"//"表示后代，
                "name"表示按名字查找，"@name"表示按属性查找
                
                "集合[条件]" 表示根据条件取集合的子集，条件可以是
                    数  值：数字,last(),last()-数字 等
                    布尔值：position()<数字,@name='条件',name='条件'
                条件是布尔值的时候可以合并计算：and or
    
    3、xquery:    基于xpath标的准查询语言，sqlserver xquery包含如下函数
                exist(xpath条件)：返回布尔值表示节点是否存在
                query(xpath条件)：返回由符合条件的节点组成的新的xml文档
                value(xpath条件,数据类型)：返回指定的标量值，xpath条件结果必须唯一
                nodes(xpath条件): 返回由符合条件的节点组成的一行一列的结果表
*/declare @data xml
set @data='
<bookstore>
<book category="COOKING">
  <title lang="en">Everyday Italian</title>
  <author>Giada De Laurentiis</author>
  <year>2005</year>
  <price>30.00</price>
</book>
<book category="CHILDREN">
  <title lang="jp">Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
</book>
<book category="WEB">
  <title lang="en">XQuery Kick Start</title>
  <author>James McGovern</author>
  <author>Per Bothner</author>
  <author>Kurt Cagle</author>
  <author>James Linn</author>
  <author>Vaidyanathan Nagarajan</author>
  <year>2003</year>
  <price>49.99</price>
</book>
<book category="WEB">
  <title lang="cn">Learning XML</title>
  <author>Erik T. Ray</author>
  <year>2003</year>
  <price>39.95</price>
</book>
</bookstore>
'--测试语句，如果不理解语法请参考上面的xpath规则和xquery函数说明--1、文档
select @data
--2、任意级别是否存在price节点
select @data.exist('//price')
--3、获取所有book节点
select @data.query('//book')
--4、获取所有包含lang属性的节点
select @data.query('//*[@lang]') 
--5、获取第一个book节点
select @data.query('//book[1]')
--6、获取前两个book节点
select @data.query('//book[position()<=2]')
--7、获取最后一个book节点
select @data.query('//book[last()]')
--8、获取price>35的所有book节点
select @data.query('//book[price>35]')
--9、获取category="WEB"的所有book节点
select @data.query('//book[@category="WEB"]')
--10、获取title的lang="en"的所有book节点
select @data.query('//book/title[@lang="en"]')
--11、获取title的lang="en"且 price>35的所有book节点
select @data.query('//book[./title[@lang="en"] or price>35 ]')
--12、获取title的lang="en"且 price>35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price>35 ]').value('(book/title)[1]','varchar(max)')
--13、等价于12
select @data.value('(//book[./title[@lang="en"] and price>35 ]/title)[1]','varchar(max)')
--14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @data.value('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]','varchar(max)')
--15、获取第一本书的title
select Tab.Col.value('(book/title)[1]','varchar(max)') as title
    from @data.nodes('bookstore')as Tab(Col) 
--16、获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as title
    from @data.nodes('//book')as Tab(Col)
--17、获取所有book的所有信息
select
 T.C.value('title[1]','varchar(max)') as title,
 T.C.value('year[1]','int') as year,
 T.C.value('title[1]','varchar(max)')as title,
 T.C.value('price[1]','float') as price,
 T.C.value('author[1]','varchar(max)') as author1,
 T.C.value('author[2]','varchar(max)') as author2,
 T.C.value('author[3]','varchar(max)') as author3,
 T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book') as T(C)
--18、获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
 T.C.value('title[1]','varchar(max)') as title,
 T.C.value('year[1]','int') as year,
 T.C.value('title[1]','varchar(max)')as title,
 T.C.value('price[1]','float') as price,
 T.C.value('author[1]','varchar(max)') as author1,
 T.C.value('author[2]','varchar(max)') as author2,
 T.C.value('author[3]','varchar(max)') as author3,
 T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book[./title[@lang!="jp"] and price>35 ]') as T(C)XML基本语法

Answer 8

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: tb
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(aaa INT,bbb INT)
Go
INSERT INTO tb
SELECT 1,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 1,4 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,5 
GO
--查询1
select 
    aaa, 
    [values]=stuff(replace(replace((select [bbb] 
                                    from tb 
                                    where aaa=t.aaa for xml AUTO),
                                    '"/><tb bbb="',','),
                            '"/>',''),1,9,'')
from tb t
group by aaa 
--查询2
SELECT * 
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 --查询3
select 
    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,4
2           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: ta
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
Go
CREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))
Go
INSERT INTO ta
SELECT 1,1,'hy3500' UNION ALL
SELECT 1,2,'aabbcc' UNION ALL
SELECT 2,3,'1111' UNION ALL
SELECT 2,4,'2222' 
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(NAME NVARCHAR(2),id INT)
Go
INSERT INTO tb
 SELECT '型号',1 UNION ALL
 SELECT '参数',2 
GO
--Start
SELECT t.[name],A,B
FROM(
    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
) t
CROSS 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) a
CROSS 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: 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 
5、最后我们来说说折分吧
如：
/*
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           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')
GO
IF OBJECT_ID('TEMPDB..#NUM') IS NOT NULL
    DROP TABLE #NUM
GO
--2000
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM FROM SYS.SYSCOLUMNS A,SYS.SYSCOLUMNS B
SELECT 
    A.ID,[VALUE]=SUBSTRING(A.[VALUE],B.ID,CHARINDEX(',',A.[VALUE]+',',B.ID)-B.ID) 
FROM 
    TB A,#NUM B
WHERE
    CHARINDEX(',',','+A.[VALUE],B.ID)=B.ID --也可用 SUBSTRING(','+A.COL2,B.ID,1)=','
--2005
;WITH T
AS
(
    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 B
WHERE
    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')
GO
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)
)BDROP TABLE tb
--查询结果
/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/