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

A mail from the Wizard himself

     

What an honour: Only hours after publishing my blog entry on dynamic crosstabs in MySQL I got an email from Giuseppe Maxia, the Wizard himself.

     

But not only did the Wizard send me a "Nice shot!" in his mail, Roland Bouman also said "Thank you guys, I learnt a lot from those!" commenting on our articles. That's actually exactly what I'd have to say to both of them myself.

     

Giuseppe Maxia, being the real Wizard, pointed out in his mail "that you don't need temporary tables to get the column values, but everything can be achieved with normal SQL (albeit not simple)." According to him there are "at least two methods to overcome the restriction on cursors with prepared queries" which forced me to use a temporary table in my example. Although he didn't go into any further details, I'm sure he'll give us some nice insights when he speaks at the Opensource Database Conference next Tuesday in Frankfurt (be sure to attend if you can, I unfortunately can't) and hopefully at the MySQL Users Conference 2006.

     

One solution I thought of when writing the stored procedure this Monday would of course be to use GROUP_CONCAT to avoid a temporary table in the first of the two dynamic queries. I chose the cursor loop over GROUP_CONCAT as a loop would give us more flexibility to enhance the procedure with more functionality. But after rethinking this I found that you could possibly achieve about 90% of what you could ever think of with nested control flow functions inside the GROUP_CONCAT itself.

     

CREATE PROCEDURE xtab2(`col_name` VARCHAR(32), `col_alias` VARCHAR(32),
                       `col_from` VARCHAR(256), `col_value` VARCHAR(32),
                       `row_name` VARCHAR(32), `row_from` VARCHAR(256))
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Generate dynamic crosstabs - variant with GROUP_CONCAT'
BEGIN

    -- Some heavy double quoting (quoting of already quoted strings) involved here
    -- to build the query that builds the list of columns for the crosstab
    SET @column_query := CONCAT('SELECT CONCAT(GROUP_CONCAT(DISTINCT ',
                                '/'//tSUM(IF(`', `col_name`, '` = ///'/', `',
                                `col_name`, '`, /'///', ', `col_value`,
                                ', 0)) AS `/', `', `col_alias`, '`, /'`/' ',
                                'SEPARATOR /',//n/'), /',//n/') INTO @xtab_query ',
                                `col_from`);

    -- Uncomment the following line if you want to see the
    -- generated query to assemble the columns
    -- SELECT @column_query;
   
    PREPARE `column_query` FROM @column_query;
    EXECUTE `column_query`;
    DEALLOCATE PREPARE `column_query`;

    SET @xtab_query = CONCAT('SELECT `', `row_name`, '`,/n',
                             @xtab_query, '/t',
                             IF(`col_value` = '1',
                                'COUNT(*)',
                                CONCAT('SUM(`', `col_value`, '`)')
                             ),
                             ' AS `total`/n',
                             `row_from`);
   
    -- Uncomment the following line if you want to see the
    -- generated crosstab query for debugging purposes
    -- SELECT @xtab_query;
   
    -- Execute crosstab
    PREPARE `xtab` FROM @xtab_query;
    EXECUTE `xtab`;
    DEALLOCATE PREPARE `xtab`;
END



     

This version is shorter than the first one and definitely more in the style of how you should normally work with a database: Don't loop over rows when you can achieve the same result with a simple query. We'll possibly loose some clarity as soon as we start to add further functionality to the GROUP_CONCAT, but we sure gain speed compared to a loop. It's just that the first of the two dynamic queries now looks like a real mess from all the quoting.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值