数据库环境:SQL SERVER 2005
有数据如图1,要求:统计每行中有多少列的值在20以内,有多少列大于20。在原表的基础上
新增2列显示,实现的结果如图2。
如果直接在原表的基础上对列进行统计,会比较麻烦,可以通过列转行生成数据集1,再对1进行
分组统计得到结果集2,然后将原表和数据集2左联接,即可实现要求。
0.数据准备
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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.列转行
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
,x1 AS ( SELECT id , attr , value FROM x0 UNPIVOT( value FOR attr IN ( one, two, three, four ) ) AS t )
2.分组统计,左联
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
(本文完)