php - 使用日期和总和旋转 mysql 结果

Question

我在 mysql 中有一些数据，我需要从中创建一些报告。我的数据来自以下查询:

SELECT StoreNbr,StoreName,Date, SUM(`Sales`) FROM sales_tbl GROUP BY StoreNbr,StoreName,Date;

这会产生以下数据(对于我的示例来说只是一小部分):

+-----------+---------------------------+----------------------------+
| StoreNbr | StoreName              | Date       | SUM(Sales)        |
+-----------+---------------------------+----------------------------+
|      1112 | Store1                | 2016-01-16 |            115.09 |
|      1112 | Store1                | 2016-01-17 |             81.00 |
|      1113 | Store2                | 2016-01-16 |            112.44 |
|      1113 | Store2                | 2016-01-17 |             56.61 |

我想将我的数据转换成这样:

+-----------+---------------------------+----------------------------+
| StoreNbr | StoreName              | 2016-01-16       | 2016-01-17  |
+-----------+---------------------------+----------------------------+
|      1112 | Store1                | 115.09           |       81.00 |
|      1113 | Store2                | 112.44           |       56.61 |

显然，查询中可能会返回数千行(存储)和未知数量的日期，因为我的查询可能会像这样运行(这将需要返回 120 多个日期列):

SELECT StoreNbr,StoreName,Date, SUM(`Sales`) FROM sales_tbl WHERE (Date BETWEEN '2016-01-10' AND '2016-05-10') GROUP BY StoreNbr,StoreName,Date;

有几种方法可以做到这一点，没有一种很简单。我做了一些研究，有些人提到 mysql 不支持数据透视。不过，我正在运行 mariadb，并且看到 mariadb 支持通过连接引擎进行旋转。虽然我无法让它工作(根据我的数据调整他们的官方示例)。另一种方法是大量的 IF 和案例，但我发现的大多数答案都很难适应，或者只根据提问者提供的数据量身定制。另一种方法是在数据出现在我的数组中时对其进行处理，因为我最后有一个 json 响应来提供数据表。 - 这是另一个我还没有弄清楚的想法。

我正在寻找一种方法来获得与日期数量无关的所需输出(我猜日期可以用周或其他任何方式代替)。谁能帮忙？

Answer 1

选择所有不同的日期

SELECT DISTINCT Date 
FROM sales_tbl 
WHERE (Date BETWEEN '2016-01-10' AND '2016-05-10')
ORDER BY Date;

并初始化一个数组，该数组由存储零的日期索引:

$dateIndexedArray = array();
while($row = $stmt1->fetch(PDO::FETCH_ASSOC) {
    $dateIndexedArray[$row['Date']] = 0;
}

arry 看起来像

[
    '2016-01-16' => 0,
    '2016-01-17' => 0
]

然后执行你的查询

SELECT StoreNbr, StoreName,Date, SUM(`Sales`) AS Sales 
FROM sales_tbl
WHERE (Date BETWEEN '2016-01-10' AND '2016-05-10')
GROUP BY StoreNbr,StoreName,Date;

并将“销售额”存储在每个商店的日期索引数组中

$report = array();
while($row = $stmt2->fetch(PDO::FETCH_ASSOC)){
    $storeIndex = $row['StoreNbr'] . ':' . $row['StoreName'];
    if (!isset($report[$storeIndex])) {
        $report[$storeIndex] = array(
            'StoreNbr'  => $row['StoreNbr'],
            'StoreName' => $row['StoreName'],
            'Sales' => $dateIndexedArray
        );
    }
    $report[$storeIndex]['Sales'][$row['Date']] = $row['Sales'];
}

$report 数组将如下所示:

[
    '1112:Store1' => [
        'StoreNbr'  => 1112,
        'StoreName' => 'Store1',
        'Sales' => [
            '2016-01-16' => 115.09,
            '2016-01-17' => 81.00
        ]
    ],
    '1113:Store2' => [
        'StoreNbr'  => 1113,
        'StoreName' => 'Store2',
        'Sales' => [
            '2016-01-16' => 112.44,
            '2016-01-17' => 56.61
        ]
    ]
]

更新:如果您需要每个商店的所有数据都在一行中，您可以将代码更改为:

$report = array();
while($row = $stmt2->fetch(PDO::FETCH_ASSOC)){
    $storeIndex = $row['StoreNbr'] . ':' . $row['StoreName'];
    if (!isset($report[$storeIndex])) {
        $report[$storeIndex] = $dateIndexedArray;
        $report[$storeIndex]['StoreNbr']  = $row['StoreNbr'];
        $report[$storeIndex]['StoreName'] = $row['StoreName'];
    }
    $report[$storeIndex][$row['Date']] = $row['Sales'];
}

结果数组如下所示:

[
    '1112:Store1' => [
        'StoreNbr'  => 1112,
        'StoreName' => 'Store1'
        '2016-01-16' => 115.09,
        '2016-01-17' => 81.
    ],
    '1113:Store2' => [
        'StoreNbr'  => 1113,
        'StoreName' => 'Store2',
        '2016-01-16' => 112.44,
        '2016-01-17' => 56.61
    ]
]

更新 2:要获取每家商店的总销售额，您可以使用 WITH ROLLUP

SELECT StoreNbr, StoreName,Date, SUM(`Sales`) AS Sales 
FROM sales_tbl
WHERE (Date BETWEEN '2016-01-10' AND '2016-05-10')
GROUP BY StoreNbr,StoreName,Date WITH ROLLUP;

$report = array();
while($row = $stmt2->fetch(PDO::FETCH_ASSOC)){
    if ($row['StoreName'] === null) {
        // Skip this row.
        // It contains total sales grouped by StoreNbr 
        // (or not grouped if StoreNbr === null).
        continue;
    }
    $storeIndex = $row['StoreNbr'] . ':' . $row['StoreName'];
    if (!isset($report[$storeIndex])) {
        $report[$storeIndex] = $dateIndexedArray;
        $report[$storeIndex]['StoreNbr']  = $row['StoreNbr'];
        $report[$storeIndex]['StoreName'] = $row['StoreName'];
    }
    if ($row['Date'] === null) {
        // This row contains total sales grouped by StoreNbr & StoreName
        $report[$storeIndex]['TotalSales'] = $row['Sales']
    } else {
        $report[$storeIndex][$row['Date']] = $row['Sales'];
    }
}

请注意，我从未使用过WITH ROLLUP。因此您可能需要调整代码。