mysql - Mysql中的数据透视表有很多动态列

Question

我在 MySQL 中制作数据透视表时遇到问题。我有下表。（这是一个简化的演示表。真实的有 4000 只股票，有 10 个交易日和 20 个测量值。）

     CREATE TABLE `levermann` (
       `RecNum` bigint(20) NOT NULL AUTO_INCREMENT,
       `Tradedate` date DEFAULT NULL,
       `Stock_Short` varchar(50) DEFAULT NULL,
       `Country` varchar(2) DEFAULT NULL,
       `LScore2` int(11) DEFAULT NULL,
       `MarketCAPUSD` bigint(20) DEFAULT NULL,
       PRIMARY KEY (`RecNum`)
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

     -- ----------------------------
     -- Records of levermann
     -- ----------------------------
     INSERT INTO `levermann` VALUES ('8099', '2018-05-23', 'ANDR.VI', 'VI', '-9', '5109518494');
     INSERT INTO `levermann` VALUES ('8100', '2018-05-23', 'BWO.VI', 'VI', '-7', '4241189324');
     INSERT INTO `levermann` VALUES ('8101', '2018-05-23', 'CAI.VI', 'VI', '-7', '3222135865');
     INSERT INTO `levermann` VALUES ('8102', '2018-05-09', 'CWI.VI', 'VI', '-8', null);
     INSERT INTO `levermann` VALUES ('8103', '2018-05-23', 'EBS.VI', 'VI', '-7', '18317742129');
     INSERT INTO `levermann` VALUES ('8104', '2018-05-23', 'FLU.VI', 'VI', '-8', '3176359049');
     INSERT INTO `levermann` VALUES ('8105', '2018-05-23', 'IIA.VI', 'VI', '-8', '2767477473');
     INSERT INTO `levermann` VALUES ('8106', '2018-05-23', 'LNZ.VI', 'VI', '-9', '3027507195');

输出应该是一个表格，其中这 8 个中的每个 STOCKCODE (fe ANDR.VI) 应该是一列，其中包含一个可选测量值 (fe LScore2)，按交易日期 (= 行) 分组。

我在 MYSQL 中找到了这个 Exmapl，但我并不完全理解它。此外，我已经这样做了：

    SELECT
    tradedate, 
    GROUP_CONCAT(stock_short) as STOCKCODE
    FROM
    levermann
    GROUP BY
    Tradedate;

但是这里的股票代码在一个单元格中，而不是在标题中。这是所需输出示例的图像。总列数约为 4000（表中的最大列数不超过 4096）。交易日期（= 行）总共约为 350 天/年，共 2 年。

很明显，列应该是动态创建的，并且不能由 AS 语句硬编码。

这个难题有什么解决办法吗？多谢。

更新：我认为像这样的动态运行语句..

    SET @sql = NULL;

    SELECT GROUP_CONCAT(concat(LScore2,' AS `LScore_',Stock_Short,'`')) into 
    @sql from levermann;

    SET @sql = CONCAT('SELECT tradedate, ', @sql, '
              FROM levermann 
               GROUP BY tradedate');

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

但我不知道如何创建字符串：

LScore AS LScore_ANDR.VI, LScore AS LScore_BWO.VI, ...

我真的很怀疑，但它可能在 MySQL 中以某种方式完成。当然我也可以制作一个php脚本。但我想了解它是如何在 MySQL 中完成的。

更新2：我想我能做到。我不太确定它是否正确，但它是动态创建的。在 MySQL 中

标签： mysqlpivot

Answer 1

我想我明白了：

SET SESSION group_concat_max_len = @@max_allowed_packet;
SET @sql = NULL;

SELECT GROUP_CONCAT(concat('MAX(CASE Stock_Short WHEN \'',Stock_Short,'\'   THEN \'',LScore2,'\' END) AS `LScore_',Stock_Short,'`')) into @sql from levermanndemo where country = 'VI';

SET @sql = CONCAT('SELECT tradedate, ', @sql, '
              FROM levermanndemo  
               GROUP BY tradedate');


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

输出：

更新：不幸的是，如果桌子看起来像这样，那就麻烦了。

我试过这个解决方案：

    SET SESSION group_concat_max_len = @@max_allowed_packet;
    SET @sql = NULL;

    SELECT  GROUP_CONCAT( DISTINCT concat('MAX(CASE WHEN p.Stock_Short = 
    \'',f.Stock_Short,'\' AND `Tradedate` = \'', f.tradedate,'\'  THEN 
    \'',f.LScore2,'\' ELSE NULL END) AS   `LScore_',f.Stock_Short,'`')) 
    into @sql from  levermanndemo f ;

    SET @sql = CONCAT('SELECT p.tradedate, ', @sql, ' FROM levermanndemo p 
    GROUP BY p.tradedate');
    #SELECT @sql; 

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

输出是不想要的——而且是错误的。现在有 2 列具有相同的 STOCK (BWO.VI 和 BWO.VI1) ，我想将这两列合并在一起。但是怎么做呢？