时间戳

首页 > 解决方案 > 获取我已转换为 HTML 表格的报告数据

php - 获取我已转换为 HTML 表格的报告数据

问题描述

我有一个当前的 SQL 查询，内容如下：

SELECT
    WEEK,
    SUM(CASE WHEN WEEK = '$week1' THEN 1 ELSE 0 END) AS week_1_total,
    SUM(CASE WHEN WEEK = '$week2' THEN 1 ELSE 0 END) AS week_2_total,
    SUM(CASE WHEN WEEK = '$week3' THEN 1 ELSE 0 END) AS week_3_total,
    SUM(CASE WHEN WEEK = '$week4' THEN 1 ELSE 0 END) AS week_4_total,
    SUM(CASE WHEN WEEK = '$week1' AND TYPE = 'refund' THEN 1 ELSE 0 END) AS week_1_refunds,
    SUM(CASE WHEN WEEK = '$week2' AND TYPE = 'refund' THEN 1 ELSE 0 END) AS week_2_refunds,
    SUM(CASE WHEN WEEK = '$week3' AND TYPE = 'refund' THEN 1 ELSE 0 END) AS week_3_refunds,
    SUM(CASE WHEN WEEK = '$week4' AND TYPE = 'refund' THEN 1 ELSE 0 END) AS week_4_refunds,
    SUM(CASE WHEN WEEK = '$week1' AND TYPE = 'closure' THEN 1 ELSE 0 END) AS week_1_closures,
    SUM(CASE WHEN WEEK = '$week2' AND TYPE = 'closure' THEN 1 ELSE 0 END) AS week_2_closures,
    SUM(CASE WHEN WEEK = '$week3' AND TYPE = 'closure' THEN 1 ELSE 0 END) AS week_3_closures,
    SUM(CASE WHEN WEEK = '$week4' AND TYPE = 'closure' THEN 1 ELSE 0 END) AS week_4_closures,
    SUM(CASE WHEN WEEK = '$week1' AND history LIKE '%rejected%' THEN 1 ELSE 0 END) AS week_1_rejections,
    SUM(CASE WHEN WEEK = '$week2' AND history LIKE '%rejected%' THEN 1 ELSE 0 END) AS week_2_rejections,
    SUM(CASE WHEN WEEK = '$week3' AND history LIKE '%rejected%' THEN 1 ELSE 0 END) AS week_3_rejections,
    SUM(CASE WHEN WEEK = '$week4' AND history LIKE '%rejected%' THEN 1 ELSE 0 END) AS week_4_rejections
FROM
    my_table
WHERE
    YEAR = '$SafeFY' AND period = '$SafePeriod'
GROUP BY
    WEEK

该查询有效并获得了我想要的数据，但是由于我的操作方式，数据已被旋转。

我只这样做了，因为这是我知道的唯一方法。我想在 PHP 循环中使用结果，如下所示：

echo '<table><opening tags> <etc...>'
while($Row = $Result->fetch_array(MYSQLI_ASSOC)) {
//HTML table rows with $Row['stuff'] here.
}
echo '</etc...></closing tags></table> '

但是数据是打印出这样的表格的错误方法：

| Week | Total  | Refunds | Closures | Rejections |
|  1   |   44   |    32   |   12     |     3      |
|  2   |   40   |    21   |   19     |     6      |
|  3   |   40   |    22   |   18     |     3      |
|  4   |   41   |    32   |   11     |     6      |

我不介意更改查询以在没有枢轴的情况下获得所有相同的结果，这样我就可以像以前那样循环它们，或者使用 PHP 数组做一些事情，这样我就可以循环这个查询结果，以更容易者为准/更好的。如何？

标签： phpsqlmariadb

解决方案

好的，我又看了一遍，我想我只是让它变得比需要的更难。

继续以下假设：1）数据库通常比 PHP 更擅长对数据进行排序/组织，因此更改查询可能比事后更改数组更好。2）如果我希望结果有 5 列，我可能需要在语句中有 5 个选择，

然后我查看了 GROUP BY 部分并意识到它已经将结果按周拆分，所以我按总和进行了修剪，然后 GROUP BY 部分将允许结果已经拆分为周，没有必要求和每周在选择部分手动。

SELECT
    WEEK,
    SUM(CASE WHEN YEAR = '2019' THEN 1 ELSE 0 END) AS total,
    SUM(CASE WHEN TYPE = 'refund' THEN 1 ELSE 0 END) AS refunds,
    SUM(CASE WHEN TYPE = 'closure' THEN 1 ELSE 0 END) AS closures,
    SUM(CASE WHEN history LIKE '%rejected%' THEN 1 ELSE 0 END) AS rejections

FROM
    my_table
WHERE
    YEAR = '$SafeFY' AND period = '$SafePeriod'
GROUP BY
    WEEK

推荐阅读