SQL输出矩阵

数据库环境:SQL SERVER2008R2

需求:用SQL实现如下2个图中的矩阵。

技术分享           技术分享

图1和图2都是行列转换的另一个变形,下面直接贴上SQL脚本。

图1的SQL实现

/*利用系统的数字辅助表,生成1-25及每连续5个数一组的组号(从1开始)*/
WITH    x0
          AS ( SELECT   ( number - 1 ) / 5 + 1 AS cn ,
                        number AS seq
               FROM     master..spt_values
               WHERE    number <= 25
                        AND number >= 1
                        AND type = P
             ),/*新增一列,按组内降序排序,及在同组内从大到小排序*/
        x1
          AS ( SELECT TOP 25
                        cn ,
                        seq ,
                        ROW_NUMBER() OVER ( ORDER BY cn, seq DESC ) dseq
               FROM     x0
               ORDER BY cn ,
                        seq
             )
    /*如果是单行号,则升序;否则,降序*/
    SELECT  MAX(CASE seq % 5
                  WHEN 1 THEN CASE cn % 2
                                WHEN 1 THEN seq
                                ELSE dseq
                              END
                END) AS A ,
            MAX(CASE seq % 5
                  WHEN 2 THEN CASE cn % 2
                                WHEN 1 THEN seq
                                ELSE dseq
                              END
                END) AS B ,
            MAX(CASE seq % 5
                  WHEN 3 THEN CASE cn % 2
                                WHEN 1 THEN seq
                                ELSE dseq
                              END
                END) AS C ,
            MAX(CASE seq % 5
                  WHEN 4 THEN CASE cn % 2
                                WHEN 1 THEN seq
                                ELSE dseq
                              END
                END) AS D ,
            MAX(CASE seq % 5
                  WHEN 0 THEN CASE cn % 2
                                WHEN 1 THEN seq
                                ELSE dseq
                              END
                END) AS E
    FROM    x1
    GROUP BY cn

图2的SQL实现

/*利用系统的数字辅助表,生成1-25及每连续5个数一组的组号(从1开始)*/
WITH    x0
          AS ( SELECT   ( number - 1 ) / 5 + 1 AS cn ,
                        number AS seq
               FROM     master..spt_values
               WHERE    number <= 25
                        AND number >= 1
                        AND type = P
             ),/*新增一列,按组内降序排序,及在同组内从大到小排序*/
        x1
          AS ( SELECT TOP 25
                        cn ,
                        seq ,
                        ROW_NUMBER() OVER ( ORDER BY cn, seq DESC ) dseq
               FROM     x0
               ORDER BY cn ,
                        seq
             ),/*按对5求余的规则新生成一个组号,根据原组号取整组的数据*/
        x2
          AS ( SELECT   seq % 5 AS sno ,
                        CASE cn
                          WHEN 1 THEN seq
                        END AS A ,
                        CASE cn
                          WHEN 2 THEN dseq
                        END AS B ,
                        CASE cn
                          WHEN 3 THEN seq
                        END AS C ,
                        CASE cn
                          WHEN 4 THEN dseq
                        END AS D ,
                        CASE cn
                          WHEN 5 THEN seq
                        END AS E
               FROM     x1
             )
    /*按新组号分组,排序*/
    SELECT  MAX(A) AS A ,
            MAX(B) AS B ,
            MAX(C) AS C ,
            MAX(D) AS D ,
            MAX(E) AS E
    FROM    x2
    GROUP BY sno
    ORDER BY A

当然,实现的方法不局限于上述2种。欢迎提出更好的解决思路。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。