求均值的问题。。。难。。

helinhai 2009-12-07 04:51:36

有一张表有1000多列（本来是10列,小梁告诉我自动生成了字段组合，所以就有了1000多列）。

现在想求每列的均值也想自动产生，而不是又一个一个的avg()
如字段 A1，A1A2,A1A3,A1A4。。。。。。。。。
1 0 1 0 。。。。。。。。。
0 1 1 0。。。。。。。。。。
均值应该 A1，A1A2,A1A3,A1A4。。。。。。。。。
0.5 0.5 , 1 , 0............

现在有加了难度了只要字段有包含A1的也要算均值（A1+A1A2+A1A3+A1A4）

怎么做容易点呢? 不要一个一个写吧

...全文

149 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

liangCK 2009-12-07

打赏
举报

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

DECLARE @tb TABLE (A1 int,A2 int,A3 int,A4 int)

INSERT INTO @tb

SELECT 1,0,1,1 UNION ALL

SELECT 0,1,0,1 UNION ALL

SELECT 1,0,0,0



--SQL查询如下:



DECLARE @x xml;

SET @x = (SELECT * FROM @tb FOR XML PATH,TYPE);



CREATE TABLE #Tmp1(localname nvarchar(128),val int,rowid int,classid int);

INSERT #Tmp1 

    SELECT *,

        classid=ROW_NUMBER() 

                     OVER(PARTITION BY rowid

                            ORDER BY CAST(RIGHT(localname,PATINDEX('%[^0-9]%',

                                                            REVERSE(localname))-1)

                                            AS int))

    FROM (

        SELECT T.x.value('local-name(.)','nvarchar(128)') AS localname,

             T.x.value('.','int') AS val,

             T.x.value('for $i in .

                         return count($i/../../*[.<<$i/..])+1','int') AS rowid

        FROM @x.nodes('//row/*') AS T(x)

    ) AS A ;





CREATE TABLE #Tmp2(localname nvarchar(128),val int,

                   rowid int,classid int,total int,

                   path nvarchar(MAX),flag int);   

;WITH Liang AS

(

    SELECT *,total=val,path=CAST(localname AS nvarchar(MAX)),

         flag = classid

    FROM #Tmp1

    UNION ALL

    SELECT A.*,B.total+A.val,CAST(B.path + A.localname AS nvarchar(MAX)),

        flag = A.classid

    FROM #Tmp1 AS A

        JOIN Liang AS B

    ON A.rowid=B.rowid AND A.classid > B.classid

)

INSERT #Tmp2 SELECT * FROM Liang;





DECLARE @columns nvarchar(MAX),@avg_Columns nvarchar(MAX);

SELECT @columns = N'',@avg_Columns=N'';

SELECT @columns = @columns + ',MAX(CASE WHEN path = '''+path+''''

                           +' AND total > 0 THEN 1 ELSE 0 END) AS ['+path+']',

       @avg_Columns = @avg_Columns + ',CAST(SUM(CASE WHEN path = '''+path+''''

                           +' AND total > 0 THEN 1 ELSE 0 END)*1.

                                  /COUNT(CASE WHEN path = '''+path

                                             +''' THEN 1 END) AS numeric(10,2))'

FROM #Tmp2 GROUP BY path;

SELECT @columns = STUFF(@columns,1,1,''),@avg_Columns=STUFF(@avg_Columns,1,1,'');



EXEC('SELECT '+@columns+' FROM #Tmp2 GROUP BY rowid

      UNION ALL 

      SELECT '+@avg_Columns +' FROM #Tmp2')





GO

DROP TABLE #Tmp1,#Tmp2;



/*

A1                                      A1A2                                    A1A2A3                                  A1A2A3A4                                A1A2A4                                  A1A3                                    A1A3A4                                  A1A4                                    A2                                      A2A3                                    A2A3A4                                  A2A4                                    A3                                      A3A4                                    A4

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

1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    0.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00

0.00                                    1.00                                    1.00                                    1.00                                    1.00                                    0.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    0.00                                    1.00                                    1.00

1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    1.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00                                    0.00

0.67                                    1.00                                    1.00                                    1.00                                    1.00                                    0.67                                    1.00                                    1.00                                    0.33                                    0.67                                    0.67                                    0.67                                    0.33                                    0.67                                    0.67





(4 行受影响)



*/