按行统计符合条件的列数

数据库环境：SQL SERVER 2005　　

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

新增2列显示，实现的结果如图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       )

　　1.列转行

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

　　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

(本文完)

原标题：按行统计符合条件的列数

关键词：