如何实现横向聚合
问题描述: 有表tb,数据如下 A
1? 2? 5? 3? 4 2? 2? 3? 4? 5 0? 3? 4? 2? 5
?
如何输出 A
1? 2? 5? 3? 4?? 5??? 1?????? 1 2? 2? 3? 4? 5?? 5??? 2?????? 1 0? 3? 5? 2? 6?? 6??? 0?????? 2
?
答: SQL Server的聚合函数是在列上的,所以可以写自定义的函数,也可以想办法把列变为行,再用聚合函数处理
?
解决示例 --测试数据 DECLARE @t TABLE(A1 int, A2 int, A3 int, A4 int, A5 int) INSERT @t SELECT 1,2,5,3,4
?
--查询 SELECT *, ??? [min] = ( ??????? SELECT MIN(v) FROM( ??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3 ??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5 ??????? )B), ??? [max] = ( ??????? SELECT MAX(v) FROM( ??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3 ??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5 ??????? )B), ??? [count>=5] = ( ??????? SELECT COUNT(*) FROM( ??????????? SELECT v=A.A1 UNION SELECT v=A.A2 UNION SELECT v=A.A3 ??????????? UNION SELECT v=A.A4 UNION SELECT v=A.A5 ??????? )B WHERE v>=5) FROM @t A
?
查询结果: A
----------- ----------- ----------- ----------- ----------- ----------- ----------- 1?????????? 2?????????? 5?????????? 3?????????? 4?????????? 1?????????? 5?????????? 1 2?????????? 2?????????? 3?????????? 4?????????? 5?????????? 2?????????? 5?????????? 1 0?????????? 3?????????? 4?????????? 2?????????? 5?????????? 0?????????? 5?????????? 1
?
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=898901 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |