mysql - 如何动态透视mysql表？

问题描述

嗨，我有以下 mysql 表：

我需要将这些数据转换为如下所示：

scadenza 并不总是相同的并且可以改变因此我相信我需要动态创建数据透视表，这是我尝试过的存储过程：

BEGIN
SELECT

    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT scadenza,  
                      importo
                 FROM ripartizione_rate
                 
    ) A;

    SET @query :=
      CONCAT(
        'SELECT DISTINCT condomino, anagrafica,', @answers,
          'FROM ripartizione_rate
       GROUP BY condomino, anagrafica'
      );
      
PREPARE statement FROM @query;
    EXECUTE statement;
END

我得到的结果非常接近，但我得到了重复的日期，如下图所示：

可以请任何人帮助我解决这个问题吗？非常感谢您的帮助

这是表格的转储文本

INSERT INTO `ripartizione_rate` (`id`, `preventivo`, `piano_rateale`, `condomino`, `anagrafica`, `immobile`, `descrizione`, `scadenza`, `stato_pagamento`, `importo`, `importo_pagato`, `importo_residuo`) VALUES
(1, 1, 1, 19, 11, 3, 'Rata Num.1', '2021-01-01', 0, '208.38', '0.00', '0.00'),
(2, 1, 1, 12, 15, 3, 'Rata Num.1', '2021-01-01', 0, '208.38', '0.00', '0.00'),
(3, 1, 1, 10, 15, 5, 'Rata Num.1', '2021-01-01', 0, '500.10', '0.00', '0.00'),
(4, 1, 1, 20, 17, 3, 'Rata Num.1', '2021-01-01', 0, '83.35', '0.00', '0.00'),
(5, 1, 1, 19, 11, 3, 'Rata Num.2', '2021-05-01', 0, '208.31', '0.00', '0.00'),
(6, 1, 1, 12, 15, 3, 'Rata Num.2', '2021-05-01', 0, '208.31', '0.00', '0.00'),
(7, 1, 1, 10, 15, 5, 'Rata Num.2', '2021-05-01', 0, '499.95', '0.00', '0.00'),
(8, 1, 1, 20, 17, 3, 'Rata Num.2', '2021-05-01', 0, '83.33', '0.00', '0.00'),
(9, 1, 1, 19, 11, 3, 'Rata Num.3', '2021-09-01', 0, '208.31', '0.00', '0.00'),
(10, 1, 1, 12, 15, 3, 'Rata Num.3', '2021-09-01', 0, '208.31', '0.00', '0.00'),
(11, 1, 1, 10, 15, 5, 'Rata Num.3', '2021-09-01', 0, '499.95', '0.00', '0.00'),
(12, 1, 1, 20, 17, 3, 'Rata Num.3', '2021-09-01', 0, '83.33', '0.00', '0.00');

标签： mysql

解决方案

这里的子查询部分：

SELECT
    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "
"
      )INTO @answers
    FROM (
      SELECT DISTINCT scadenza,          <----
                      importo            <---- this subquery
                 FROM ripartizione_rate  <---- 
    ) A

返回以下结果：

+------------+---------+
| scadenza   | importo |
+------------+---------+
| 2021-01-01 |  208.38 |
| 2021-01-01 |  500.10 |
| 2021-01-01 |   83.35 |
| 2021-05-01 |  208.31 |
| 2021-05-01 |  499.95 |
| 2021-05-01 |   83.33 |
| 2021-09-01 |  208.31 |
| 2021-09-01 |  499.95 |
| 2021-09-01 |   83.33 |
+------------+---------+

由于的DISTINCT组合，每个日期返回 3 行scadenza, importo。如果您SELECT @answers;在分配变量后运行，那么您将获得：

SELECT @answers;
+-------------------------------------------------------------+
|  @answers                                                   |
+-------------------------------------------------------------+
| MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01'  |
| ,MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01' |
| ,MAX(IF(scadenza='2021-01-01',importo ,'')) AS '2021-01-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-05-01',importo ,'')) AS '2021-05-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
| ,MAX(IF(scadenza='2021-09-01',importo ,'')) AS '2021-09-01' |
+-------------------------------------------------------------+

而您真正想要的只是 3 个不同的日期，而不是 3x3 的不同日期。因此，修复非常简单，您只需要从子查询中删除该列importo：

SELECT
    GROUP_CONCAT(
    CONCAT("MAX(IF(scadenza='", scadenza, "',importo ,'')) AS '", scadenza, "'"), "") INTO @answers
    FROM (SELECT DISTINCT scadenza
            FROM ripartizione_rate
    ) A;
    
 SELECT @answers;
 SET @query :=
      CONCAT(
        'SELECT DISTINCT condomino, anagrafica,', @answers2,
          'FROM ripartizione_rate
       GROUP BY condomino, anagrafica
       ORDER BY condomino, anagrafica'
      );

SELECT @query;

PREPARE statement FROM @query;
EXECUTE statement;

演示小提琴

mysql - 如何动态透视mysql表？

问题描述

解决方案

推荐阅读