数据库环境：SQL SERVER 2005
有数据如图1，要求：统计每行中有多少列的值在20以内，有多少列大于20。在原表的基础上
新增2列显示，实现的结果如图2。
如果直接在原表的基础上对列进行统计，会比较麻烦，可以通过列转行生成数据集1，再对1进行
分组统计得到结果集2，然后将原表和数据集2左联接，即可实现要求。
0.数据准备
WITH x0AS ( SELECT 1 AS id ,2 AS one ,8 AS two ,13 AS three ,15 AS fourUNION ALLSELECT 2 AS id ,9 AS one ,11 AS two ,25 AS three ,36 AS fourUNION ALLSELECT 3 AS id ,3 AS one ,4 AS two ,5 AS three ,9 AS fourUNION ALLSELECT 4 AS id ,8 AS one ,13 AS two ,40 AS three ,42 AS fourUNION ALLSELECT 5 AS id ,1 AS one ,3 AS two ,9 AS three ,21 AS four)
1.列转行
,x1AS ( SELECT id ,attr ,valueFROM x0 UNPIVOT( value FOR attr IN ( one, two, three, four ) ) AS t)
2.分组统计，左联
SELECT x0.id ,one ,two ,three ,four ,count1 ,count2FROM x0LEFT JOIN ( SELECT id ,SUM(CASE WHEN value <= 20 THEN 1ELSE 0END) AS count1 ,SUM(CASE WHEN value > 20 THEN 1ELSE 0END) AS count2FROM x1GROUP BY id) x2 ON x2.id = x0.id
(本文完)
