SQL输出矩阵

浏览数：18 / 时间：2015年06月12日

数据库环境: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