时间戳

首页 > 解决方案 > 在数据表中按月对数据库表中的数据进行分组

php - 在数据表中按月对数据库表中的数据进行分组

问题描述

我目前有一个数据表，其中包含我所做工作的信息。

 ID  |    date    |  income  |  payment  |  profit
==================================================
  1  | 2019/01/01 |   100    |    75     |   25
  2  | 2019/01/03 |   200    |    150    |   50
  3  | 2019/02/02 |   350    |    200    |   150
  4  | 2019/04/05 |   100    |    75     |   25
  5  | 2019/05/03 |   500    |    300    |   200
  6  | 2019/07/07 |   200    |    160    |   40

我希望使用 highcharts.js 将这些数据转换为条形图，但首先我需要以下表的形式按月对所有内容进行分组

Month   |  Income  |  Payment  |  Profit
========================================
January |   300    |   225     |  75
February|   350    |   200     |  50
March   |    0     |    0      |  0
April   |   100    |   75      |  25
May     |   700    |   460     |  240

我不确定如何根据日期查找值。

我一直在寻找这样的东西：

select date ,income, payment, From table1
    SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `income`) as jan_income
    SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `payment`) as jan_payment
    SUM( CASE WHEN DATE_FORMAT(date,'%m/%Y') = 01/2019 THEN `profit`) as jan_profit

并使用填充table2表格

if (mysqli_num_rows($query) > 0) {
    // output data of each row
    while($result = mysqli_fetch_assoc($query)) {
    echo "<tr>
            <td>January</td>
            <td>".$result['jan_income']."</td>
            <td>".$result['jan_payment']."</td>
            <td>".$result['jan_profit']."</td>
          </tr>
          <tr>
            <td>February</td>
            <td>".$result['feb_income']."</td>
            <td>".$result['feb_payment']."</td>
            <td>".$result['feb_profit']."</td>
          </tr>

如果有人能就实现这一目标的正确方法提出建议，我将不胜感激。

更新...基于 NBK 答案的新 PHP

<tbody>
    <?php
     //Connect to database (same connection details as all tables so know this works)
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $sql = "SELECT date, income, payment FROM add_job
            DATE_FORMAT(`date`,'%M %Y') AS MontnameYear, 
            MIN(DATE_FORMAT(`date`,'%M')) AS MonthName, 
            SUM(`income`) AS income,
            SUM(`payment`) AS payment,
            MIN(`date`) AS mdate
            GROUP BY DATE_FORMAT(`date`,'%M %Y')
            ORDER by mdate;";

    $query = mysqli_query($conn, $sql);

    if (mysqli_num_rows($query) > 0) {
        // output data of each row
        while($result = mysqli_fetch_assoc($query)) {
            echo "<tr>
                      <td>".$result['MonthName']."</td>
                      <td>".$result['income']."</td>
                      <td>".$result['payment']."</td>
                      <td>".$result['profit']."</td>
                  </tr>";
            }
        } 
        else { 
            echo "0 results";
        }
    mysqli_close($conn);
?>
</tbody>

标签： phpjquerymysqlsqldatatables

解决方案

当您使用此 sql 状态时，会更容易，emt

Select
  DATE_FORMAT(`date`,'%M %Y') MontnameYear,MIN(DATE_FORMAT(`date`,'%M')) MonthName
  , SUM(`income`) income,SUM(`payment`) payment, SUM(`profit`) profit
  ,MIN(`date`) mdate
FROM table1
GROUP BY DATE_FORMAT(`date`,'%M %Y')
ORDER by mdate;

解释：

MonthnameYear 需要对选择进行分组以获得正确的收入...

MonthName 是在您的表格中显示正确的 Monthname，因为您想要它，所以会在 2019 年 1 月

SUM(..) 很明显，它可以让你得到按月和年分组的列的总和

mdate 我必须正确排序，所以我必须使用实际日期进行排序

DBfiddle 示例

这给了你这个结果

MontnameYear    MonthName   income  payment     profit  mdate
January 2019    January     300     225         75      2019-01-01 01:00:00
February 2019   February    350     200         150     2019-02-02 01:00:00
April 2019      April       100     75          25      2019-04-05 02:00:00
May 2019        May         500     300         200     2019-05-03 02:00:00
July 2019       July        200     160         40      2019-07-07 02:00:00

然后在你的 php 代码中做这个，你只有一个表行，它是循环的，它产生了相同的结果。

while($result = mysqli_fetch_assoc($query)) {
  echo "<tr>
          <td>".$result['MonthName']."</td>
          <td>".$result['income']."</td>
          <td>".$result['payment']."</td>
          <td>".$result['profit']."</td>
        </tr>"
 }

推荐阅读