字符串中数据的运算和统计

问题:

数据表如下

 

cate            values                                  dates

 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-04-01

 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01

 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-04-01

 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-04-01

 A1           12.32,15.6,14.5,45.00,78.12,23.45         2006-05-01

 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01

 A1           12.31,15.6,14.5,44.00,78.12,23.45         2006-05-01

 A2           14.56,67.23,45.12,23.1,34.67,56.98        2006-05-01

 A1           -12.32,15.6,14.5,45.00,78.12,23.45         2006-04-01

 A2           12.32,-15.6,14.5,无,78.12,23.45         　2006-04-01

不用存储过程实现如下数据统计

 cate            T1                                     dates

A1   4.10333,15.6,14.5,44.6667,78.12,23.45              2006-04-01

A1   12.315,15.6,14.5,44.5,78.12,23.45                  2006-05-01

A2   13.8133,39.62,34.9133,15.4,49.1533,45.8033         2006-04-01

A2   14.56,67.23,45.12,23.1,34.67,56.98                 2006-05-01

==========================================================================

就是values 值之间用,隔开的,当cate和dates 相同时,算values中各个以,隔开的数值的平均值,要求是不用存储过程实现

 

 

 

create table up(

cate  varchar(3),

[values]  varchar(50),

dates datetime

)

 

insert up select

 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-04-01'

union all select

 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'

union all select

 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-04-01'

union all select

 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-04-01'

union all select

 'A1','12.32,15.6,14.5,45.00,78.12,23.45','2006-05-01'

union all select

 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'

union all select

 'A1','12.31,15.6,14.5,44.00,78.12,23.45','2006-05-01'

union all select

 'A2','14.56,67.23,45.12,23.1,34.67,56.98','2006-05-01'

union all select

 'A1','-12.32,15.6,14.5,45.00,78.12,23.45','2006-04-01'

union all select

 'A2','12.32,-15.6,14.5,无,78.12,23.45','2006-04-01'

 

go

 

;WITH Liang AS

(

    SELECT

        A.cate,

        A.dates,

        B.x.value('.','varchar(20)') AS value,

        B.x.value('

               for $i in .

                   return count( ($i/../*[$i>>.]) )+1

            ','int') AS cnt

    FROM (

        SELECT

            cate,

            CONVERT(xml,'<v>'+REPLACE([values],',','</v><v>')+'</v>') AS [values],

            dates

        FROM up

    ) AS A

        CROSS APPLY A.[values].nodes('//v') AS B(x)

),

Liang2 AS

(

    SELECT

        cate,

        dates,

        cnt,

        AVG(CASE WHEN ISNUMERIC(value)=1 THEN CAST(value AS DECIMAL(18,2)) ELSE 0 END) AS value

    FROM Liang

    GROUP BY cate,dates,cnt

)

SELECT

    A.cate,

    A.dates,

    STUFF(B.[values],1,1,'') AS [values]

FROM (

    SELECT DISTINCT

        cate,dates

    FROM Liang2

) AS A

    CROSS APPLY(

        SELECT [values]=(

                    SELECT

                        ','+CAST(value AS VARCHAR) AS [text()]

                    FROM Liang2

                    WHERE cate=A.cate

                        AND dates=A.dates

                    ORDER BY cnt

                    FOR XML PATH('')

               )

    ) AS B

 

go

DROP TABLE up