mysql - 具有用户定义时间范围的数据透视表

Question

我已经为我的数据库结构和迄今为止构建的查询创建了一个 dbfiddle。

用户应该能够选择一个时间范围（以月为单位），并且运行此查询应该会看到一个报告，在您拥有移动的行和在您拥有月份的列中。因此，如果用户选择“2021-01”和“2021-06”，我希望只显示 jan-jun 列。如果用户选择“2020-07”和“2021-02”，则应仅选择这些列两年之间传播）。

到目前为止，我的查询如下：

SELECT
    movement,  
    SUM(IF(MONTH(date) = 1, amount, 0)) AS Jan,
    SUM(IF(MONTH(date) = 2, amount, 0)) AS Feb,
    SUM(IF(MONTH(date) = 3, amount, 0)) AS Mar,
    SUM(IF(MONTH(date) = 4, amount, 0)) AS Apr,
    SUM(IF(MONTH(date) = 5, amount, 0)) AS May,
    SUM(IF(MONTH(date) = 6, amount, 0)) AS Jun,
    SUM(IF(MONTH(date) = 7, amount, 0)) AS Jul,
    SUM(IF(MONTH(date) = 8, amount, 0)) AS Aug,
    SUM(IF(MONTH(date) = 9, amount, 0)) AS Sep,
    SUM(IF(MONTH(date) = 10, amount, 0)) AS Oct,
    SUM(IF(MONTH(date) = 11, amount, 0)) AS Nov,
    SUM(IF(MONTH(date) = 12, amount, 0)) AS Dece
FROM movements
WHERE YEAR(date)=2021
GROUP BY movement

作为旁注：我正在运行 mysql8，所以也许我的代码可以通过利用一些我还不知道的 mysql8 功能来改进。

标签： mysqlpivotpivot-tabledynamic-pivot