你的位置:首页 > 数据库

[数据库]SQL输出矩阵


数据库环境:SQL SERVER2008R2

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

图1           图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种。欢迎提出更好的解决思路。