字符串列取值相加减更新到新列(XML)

现在有表

scores1 xt1 scores2 xt2 scores xt
10 2,3,2,3 12 2,6,2,2  
5.5 1,2,1,1.5 10 2,3,2,3  

求这样结果

scores1 xt1 scores2 xt2 scores xt
10 2,3,2,3 12 2,6,2,2 11 2,4.5,2,2.5
5.5 1,2,1,1.5 10 2,3,2,3 7.75 1.5,2.5,1.5,2.25 

说明
xt=(xt1[1]+xt2[1])/2+(xt1[2]+xt2[2])/2+(xt1[3]+xt2[3])/2+(xt1[4]+xt2[4])/2
scores=xt[1]+xt[2]+xt[3]+xt[4]

注意： xt1和xt2中均为,隔开的字串，也可以看做一个数组，这个是不定长的，上面的是4个值，也可能是3个值。
求两个xt各个对应值的平均值和相加后的总和

 

 

原贴:http://topic.csdn.net/u/20100404/11/04368ae6-e985-45ee-bed1-4e962c5ed74b.html?84949

 

 

--------------------------------------------------------------------------

--  Author : htl258(Tony)

--  Date   : 2010-04-04 12:24:57

--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

--          Mar 29 2009 10:27:29

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

--------------------------------------------------------------------------

--> 生成测试数据表:tb

 

IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([scores1] DECIMAL(18,1),[xt1] NVARCHAR(10),[scores2] DECIMAL(18,1),[xt2] NVARCHAR(10),[scores] NVARCHAR(20),[xt] NVARCHAR(20))

INSERT [tb]

SELECT 10,'2,3,2,3',12,'2,6,2,2',NULL,NULL UNION ALL

SELECT 5.5,'1,2,1,1.5',10,'2,3,2,3',NULL,NULL

GO

-->SQL查询如下：

--代码作者:happyflystone 修改并格式化:Tony

UPDATE TB SET

    SCORES = CAST((SCORES1+SCORES2)/2 AS FLOAT),

    XT = STUFF(

       (

           SELECT ','+LTRIM(CAST((COLA + COLB)/2.00 AS FLOAT)) AS [data()]

           FROM (

              SELECT CAST('<R>'+REPLACE('<R1>'+XT1+'</R1>',',','</R1><R1>') +

                 REPLACE('<R2>'+XT2+'</R2>',',','</R2><R2>')+'</R>' AS XML) AS T

                                                                      --T为字段名

              ) AS A

              CROSS APPLY (

                     SELECT COLA =T.X.value('.','NUMERIC(10,2)'),

                         IDX= ROW_NUMBER() OVER(ORDER BY GETDATE())

                     FROM A.T.nodes('//R/R1') AS T(X)

                  ) AS B

              CROSS APPLY (

                     SELECT COLB =T.X.value('.','NUMERIC(10,2)'),

                         IDX= ROW_NUMBER() OVER(ORDER BY GETDATE())

                     FROM A.T.nodes('//R/R2') AS T(X)

                  ) C

           WHERE B.IDX = C.IDX

    FOR XML PATH('')),1,1,'')

 

SELECT * FROM TB

 

/*
scores1    xt1           scores2   xt2            scores       xt
-------   ---------   -------    ---------   --------     -----------------
10.0       2,3,2,3      12.0        2,6,2,2       11            2 ,4.5 ,2 ,2.5
5.5        1,2,1,1.5    10.0        2,3,2,3       7.75         1.5 ,2.5 ,1.5 ,2.25

 

(2 行受影响)
*/