按行统计符合条件的列数

数据库环境:SQL SERVER 2005  

  有数据如图1,要求:统计每行中有多少列的值在20以内,有多少列大于20。在原表的基础上

新增2列显示,实现的结果如图2。

  图1         图2

  如果直接在原表的基础上对列进行统计,会比较麻烦,可以通过列转行生成数据集1,再对1进行

分组统计得到结果集2,然后将原表和数据集2左联接,即可实现要求。

  0.数据准备

WITH    x0
          AS ( SELECT   1 AS id ,
                        2 AS one ,
                        8 AS two ,
                        13 AS three ,
                        15 AS four
               UNION ALL
               SELECT   2 AS id ,
                        9 AS one ,
                        11 AS two ,
                        25 AS three ,
                        36 AS four
               UNION ALL
               SELECT   3 AS id ,
                        3 AS one ,
                        4 AS two ,
                        5 AS three ,
                        9 AS four
               UNION ALL
               SELECT   4 AS id ,
                        8 AS one ,
                        13 AS two ,
                        40 AS three ,
                        42 AS four
               UNION ALL
               SELECT   5 AS id ,
                        1 AS one ,
                        3 AS two ,
                        9 AS three ,
                        21 AS four
             )
View Code

  1.列转行

,x1
          AS ( SELECT   id ,
                        attr ,
                        value
               FROM     x0 UNPIVOT( value FOR attr IN ( one, two, three, four ) ) AS t
             )
View Code

  2.分组统计,左联

SELECT  x0.id ,
            one ,
            two ,
            three ,
            four ,
            count1 ,
            count2
    FROM    x0
            LEFT JOIN ( SELECT  id ,
                                SUM(CASE WHEN value <= 20 THEN 1
                                         ELSE 0
                                    END) AS count1 ,
                                SUM(CASE WHEN value > 20 THEN 1
                                         ELSE 0
                                    END) AS count2
                        FROM    x1
                        GROUP BY id
                      ) x2 ON x2.id = x0.id
View Code

(本文完)

posted on 2015-08-27 22:13  ToBeHJH  阅读(1082)  评论(0编辑  收藏  举报

导航