如何将MySQL用于动态数据透视表或交叉表 [英] how to use MySQL for a dynamic pivot table or crosstab

问题描述

我知道这个问题或其变体已经被问过多次了，我什至尝试实现一种解决方案，但是我在努力解决这个问题上很困难.

I know this question or a variance thereof has been asked multiple times, and I have even tried implementing a solution, but I am struggling in completing it.

我有一个非常简单的表，其中包含三个数据列:Date1，Report _#，Name.我想将其围绕Date和各种名称作为标题标签，并在下面显示所有报告编号.

I have a very simple table with three data columns: Date1, Report_#, Name. I would like to pivot it around Date and the various names as the header tab, and have all the report numbers appear below.

所以它看起来像这样:

Report | Date   | Name                     Date   | Name1 | Name2 | Name3
-----------------------                    ------------------------------
1      | 4-5-12 | Name1                    4-5-12 | 1     | 2     | 3
2      | 4-5-12 | Name2      ----->        4-6-12 | 4     | 5     | 6
3      | 4-5-12 | Name3                    4-7-12 | 7     | 8     | 9
4      | 4-6-12 | Name1                    
5      | 4-6-12 | Name2                    
6      | 4-6-12 | Name3                    
7      | 4-7-12 | Name1
8      | 4-7-12 | Name2
9      | 4-7-12 | Name3

我能够从 http://www了解如何处理. artfulsoftware.com/infotree/queries.php#78 但我被卡住了.

I was able to get an idea of what to do from http://www.artfulsoftware.com/infotree/queries.php#78 but I'm stuck.

我能够手动隔离列名，并在每个名称下列出报告#，但是我想动态地找出不同的名称，将它们命名为列名，并列出相应的报告.

I was able to isolate the column names manually and list the report # under each name, but I want to do dynamically figure out the distinct names, make them column names, and list the corresponding reports.

因此，我创建了一个过程，该过程将查找不同的名称并输出正确的代码.现在，我很难将过程的结果插入查询中.而且上面的链接无济于事，(似乎跳过了这一步).

So I created a procedure that would find distinct names and output the correct code. Now I have a hard time plugging the results of the procedure onto the query. And the link above does not help whatsoever, (it seems to skip that step).

这是手动方式的代码:

SELECT `DATE`, 
GROUP_CONCAT(CASE `Name` WHEN 'Name1' THEN `Report` END) AS 'Name1', 
GROUP_CONCAT(CASE `Name` WHEN 'Name2' THEN `Report` END) AS 'Name2' 
FROM `report_db` GROUP BY `DATE` ORDER BY `DATE`;

这是为数据库中所有不同名称动态打印GROUP_CONCAT(...)的代码:

And here is the code that dynamic prints the GROUP_CONCAT(... for all the distinct names in the database:

DROP PROCEDURE IF EXISTS writecountpivot;
DELIMITER | 
CREATE PROCEDURE writecountpivot( db CHAR(64), tbl CHAR(64), col CHAR(64) ) 
BEGIN 
DECLARE datadelim CHAR(1) DEFAULT '"'; 
DECLARE singlequote CHAR(1) DEFAULT CHAR(39); 
DECLARE comma CHAR(1) DEFAULT ','; 
SET @sqlmode = (SELECT @@sql_mode); 
SET @@sql_mode=''; 
  SET @sql = CONCAT( 'SELECT DISTINCT CONCAT(', singlequote, 
                     ',group_concat(IF(', col, ' = ', datadelim, singlequote, comma, 
                     col, comma, singlequote, datadelim, comma, '`IR NO`,null)) AS `',  
                     singlequote, comma, col, comma, singlequote, '`', singlequote,  
                     ') AS countpivotarg FROM ', db, '.', tbl, 
                     ' WHERE ', col, ' IS NOT NULL' ); 
  -- UNCOMMENT TO SEE THE MIDLEVEL CODE: 
  -- SELECT @sql;  
  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DROP PREPARE stmt; 
  SET @@sql_mode=@sqlmode; 
END
| 
DELIMITER ; 
CALL writecountpivot('database','`report_db`','name');

，上面代码的结果就是这个

and the result of the code above would be this

,group_concat(IF(name = "Name1",`IR NO`,null)) AS `Name1`
,group_concat(IF(name = "Name2",`IR NO`,null)) AS `Name2`
,group_concat(IF(name = "Name3",`IR NO`,null)) AS `Name3`

**那么，如何获取此文本并将其插入到我的SQL中?如何将过程和查询放在一起?**

** So how do I take this text and plug it to my SQL? How do I put the procedure and the query together?**

推荐答案

很难准确说明您要执行的操作，但是如果您要创建一个可以执行任意数据透视的过程，那么您会将需要为其提供更多参数(例如，要旋转的列和可以找到值的列).

It's difficult to tell exactly what it is you're trying to do, but if you're attempting to create a procedure that can perform arbitrary pivots then you'll need to provide it with more arguments (such as the column on which to pivot and the column in which values can be found).

此外，您将需要在准备好的语句中创建准备好的语句.最外面的语句将基于指定列中的唯一值，使用GROUP_CONCAT()构造要执行的GROUP_CONCAT()表达式:

Furthermore, you will need to create a prepared statement from within a prepared statement. The outermost statement will use GROUP_CONCAT() to construct the GROUP_CONCAT() expressions that are to be executed, based on the unique values in the specified column:

CREATE FUNCTION SQL_ESC(_identifier VARCHAR(64))
RETURNS VARCHAR(130) DETERMINISTIC
RETURN CONCAT('`',REPLACE(_identifier,'`','``'),'`')//

CREATE PROCEDURE writecountpivot(
  IN _db_nm VARCHAR(64),
  IN _tb_nm VARCHAR(64),
  IN _cl_gp VARCHAR(64),
  IN _cl_pv VARCHAR(64),
  IN _cl_vl VARCHAR(64)
) BEGIN
  SET @sql := CONCAT(
    "SELECT CONCAT('
              SELECT   ",SQL_ESC(_cl_gp),",',
              GROUP_CONCAT(DISTINCT CONCAT(
                'GROUP_CONCAT(IF(",
                   SQL_ESC(_cl_pv),"=',QUOTE(",SQL_ESC(_cl_pv),"),'
                 , ",SQL_ESC(_cl_vl),"
                 , NULL
                 )) AS ',SQL_ESC(",SQL_ESC(_cl_pv),")
              )), '
              FROM     ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm),"
              GROUP BY ",SQL_ESC(_cl_gp),"
            ')
     INTO   @sql
     FROM   ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm)
  );

  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DEALLOCATE PREPARE stmt;

  PREPARE stmt FROM @sql; 
  EXECUTE stmt; 
  DEALLOCATE PREPARE stmt;
END//

在 sqlfiddle 上查看.

这篇关于如何将MySQL用于动态数据透视表或交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！