使用MySQL存储过程创建动态交叉表（3）

使用 MySQL 存储过程创建动态交叉表

 

 

今天几乎看了一天关于 SQL 生成 Crosstab 的话题。从网上找了很多例子，也学到一些东西，现在简单总结：

 

我的有一个 demand 表：

 

DROP TABLE IF EXISTS `xcvrs`.`demand`;

CREATE TABLE  `xcvrs`.`demand` (

  `item_code` varchar(15) NOT NULL,      /* 产品代码 */

  `week_code` varchar(20) NOT NULL,     /* 周代码 */

  `dmd_qty` int(10) unsigned NOT NULL   /* 所需数量 */

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

我想生成一个 Crosstab ，纵轴是 item_code ，横轴是 week_code ，体是 dmd_qty 的和。网上给出了两种方法：

l            直接用 SQL 语句实现，通常用于横轴列固定情况；

l            使用存储过程，通常可用于横轴列不固定情况。

 

1.       直接用 SQL 语句实现

很简单，基本用到的就是 case 语句：

SELECT `item_code`,

SUM(CASE week_code WHEN 'Feb-Wk01' THEN dmd_qty ELSE 0 END) as `Feb-Wk01`,

SUM(CASE week_code WHEN 'Feb-Wk02' THEN dmd_qty ELSE 0 END) as `Feb-Wk02`,

SUM(CASE week_code WHEN 'Feb-Wk03' THEN dmd_qty ELSE 0 END) as `Feb-Wk03`,

SUM(CASE week_code WHEN 'Feb-Wk04' THEN dmd_qty ELSE 0 END) as `Feb-Wk04`

FROM `demand` GROUP BY `item_code`

 

 

2.       使用存储过程

我查了 N 多中文文章，几乎一窝蜂全都是 SQL Server 的例子。 MySQL 的例子少之又少，而且没找到能够顺利运行的。我本来对存储过程就不太熟，这下更加头昏脑胀。

无奈晚上 google ，间接搜到两篇文章，我已经转贴在我的 blog 里了：

l            The Wizard revisited: Dynamic Crosstabs using MySQL Stored Procedures

l            A mail from the Wizard himself

 

看了这两篇文章，摸到了一些头绪。将上面的 CASE 语句利用循环生成动态 SQL ，再执行获得查询结果。

 

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `xcvrs`.`xtab02` $$

CREATE PROCEDURE `xcvrs`.`xtab02` ()

BEGIN

 

    DECLARE `xtab_query`      VARCHAR(4096)  DEFAULT '';

    DECLARE `done`           INT            DEFAULT 0;

    DECLARE `temp_week`      VARCHAR(10);

 

    DECLARE `column_cursor` CURSOR FOR

        SELECT DISTINCT `week_code` FROM `demand` ORDER BY `week_code`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;

 

 

    /* Create the CASE phrase via loop */

    OPEN `column_cursor`;

    column_loop: LOOP

        FETCH `column_cursor` INTO `temp_week`;

        IF `done` THEN LEAVE column_loop; END IF;

        SET `xtab_query` = CONCAT(`xtab_query`,

                                  ', SUM(CASE `week_code` WHEN /'',

                                  temp_week,

                                  '/' THEN dmd_qty ELSE 0 END) AS `',

                                  temp_week,

                                   '`');

    END LOOP column_loop;

    CLOSE `column_cursor`;

 

 

    /* Connect the SQL parts */

    SET `xtab_query` = CONCAT('SELECT `item_code`',

                            `xtab_query`, ' FROM demand GROUP BY `item_code`');

 

    /* Execute crosstab */

    SET @xtab_query = `xtab_query`;

    PREPARE `xtab02` FROM @xtab_query;

    EXECUTE `xtab02`;

    DEALLOCATE PREPARE `xtab02`;

 

 

END $$

 

DELIMITER ;

 

然后执行 call xtab02() ，即得到所需结果。