使用动态列透视 MySQL 表 [英] Pivot MySQL Table with Dynamic Columns

问题描述

我发布了一个我之前在以某种方式表示数据时遇到的问题，在发布 2 个帖子后，我能够获得足够的信息来意识到我需要的是一个带有动态列的数据透视表.

I posted on an issue I was having before in representating data in a certain way and after 2 posts I was able to get enough information to realise what I need is a pivot table with dynamic columns.

我已经能够创建一个效果很好的静态数据透视表，但对于严肃的报告，它们毫无用处.这是我到目前为止所做的:

I have been able to create a static pivot table with great effect but for serious reporting they are useless. Here is what I have done so far:

select `title` as `Payment Method`,
  sum(case when MONTH(t1.`month_payment`) = 1 then amount else 0 end) Jan,
  sum(case when MONTH(t1.`month_payment`) = 2 then amount else 0 end) Feb,
  sum(case when MONTH(t1.`month_payment`) = 3 then amount else 0 end) Mar,
  sum(case when MONTH(t1.`month_payment`) = 4 then amount else 0 end) Apr,
  sum(case when MONTH(t1.`month_payment`) = 5 then amount else 0 end) May,
  sum(case when MONTH(t1.`month_payment`) = 6 then amount else 0 end) Jun,
  sum(case when MONTH(t1.`month_payment`) = 7 then amount else 0 end) Jul,
  sum(case when MONTH(t1.`month_payment`) = 8 then amount else 0 end) Aug,
  sum(case when MONTH(t1.`month_payment`) = 9 then amount else 0 end) Sep,
  sum(case when MONTH(t1.`month_payment`) = 10 then amount else 0 end) Oct,
  sum(case when MONTH(t1.`month_payment`) = 11 then amount else 0 end) Nov,
  sum(case when MONTH(t1.`month_payment`) = 12 then amount else 0 end) `Dec`

from record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup

这可以为我提供 1 月到 12 月的静态报告，但问题是如果用户从 2015 年 1 月到 2017 年 12 月进行搜索，该表会将 2015 年 1 月、2016 年和 2017 年的所有日期汇总并汇总到显示的 1 月给错误的用户.

this is able to get me a static report from January to December but the issue is if the user searches from January 2015 to December 2017, the table will sum and aggregate Jan 2015, 2016 and 2017 all into the January that is displayed to the user which is wrong.

我还为年度报告制作了一个静态数据透视:

I have also produced a static pivot for yearly report:

select `title` as `Payment Method`,
  sum(case when YEAR(t1.`month_payment`) = 2013 then amount else 0 end) `2013`,
  sum(case when YEAR(t1.`month_payment`) = 2014 then amount else 0 end) `2014`,
  sum(case when YEAR(t1.`month_payment`) = 2015 then amount else 0 end) `2015`,
  sum(case when YEAR(t1.`month_payment`) = 2016 then amount else 0 end) `2016`,
  sum(case when YEAR(t1.`month_payment`) = 2017 then amount else 0 end) `2017`

from record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup

但有时您需要报告，在很长一段时间内(例如 12 岁以上)以月为单位提供年度细分，以便您可以看到趋势.

but sometimes you need reports that give you the breakdown of the year in months over a long period (e.g 12+) so you can see a trend.

我尝试进行自动旋转并不那么成功:

My attempt to do an automatic pivot was not so successful:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when month_payment = ''',
      month_payment,
      ''' then amount end) AS `',
      month_payment,
      '`'
    )
  ) INTO @sql
FROM  record_payment;

SET @sql = CONCAT('SELECT `title` as `Payment Method`, ', @sql, ' FROM record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

感谢任何帮助.

推荐答案

我猜你的 month_payment 列是 DATE (顺便说一句，应该发布 SHOW CREATETABLE 当你问 SQL 问题时，所以我们不必猜测).

I guess your month_payment column is a DATE (btw, should post the SHOW CREATE TABLE when you ask SQL questions so we don't have to guess).

但是您的第一个查询没有将其格式化为年/月.也不限制日期范围.

But your first query is not formatting that to the year/month. Nor is it limiting the date range.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ',
      EXTRACT(YEAR_MONTH FROM month_payment),
      ' THEN AMOUNT END) AS `',
      EXTRACT(YEAR_MONTH FROM month_payment),
      '`'
    )
  ) INTO @sql
FROM record_payment
WHERE month_payment BETWEEN ? AND ?

参见 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_extract

尽管有 Barmar 的建议，但没有理由将其放入存储过程中.你用 PHP 标记了你的问题，你可以在 PHP 中做到这一点:

Despite Barmar's suggestion, there's no reason to put this in a stored procedure. You tagged your question with PHP, and you can do this in PHP just fine:

<?php

...get a PDO connection...

$sql = "
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ',
          EXTRACT(YEAR_MONTH FROM month_payment),
          ' THEN AMOUNT END) AS `',
          EXTRACT(YEAR_MONTH FROM month_payment),
          '`'
        )
      ) AS `pivot_columns`
    FROM record_payment
    WHERE month_payment BETWEEN ? AND ?
";
$stmt = $pdo->prepare($sql);
$date_from = '2017-01-01';
$date_to   = '2017-08-01';
$stmt->execute([$date_from, $date_to]);
$row = $stmt->fetch();
$stmt->closeCursor();
$pivot_columns = $row['pivot_columns'];

$sql = "
    SELECT title AS `Payment Method`, {$pivot_columns}
    FROM record_payment t1
    JOIN setting_payment_method spm ON spm.id = t1.method_id
    WHERE month_payment BETWEEN ? AND ?
    GROUP BY title WITH ROLLUP
";
echo $sql;
$stmt = $pdo->prepare($sql);
$stmt->execute([$date_from, $date_to]);
$results = $stmt->fetchAll();
$stmt->closeCursor();

print_r($results);

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