你的位置:首页 > 数据库

[数据库]按行统计符合条件的列数


数据库环境: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

(本文完)