时间戳

首页 > 解决方案 > 加入两个表，求和，并按每月的星期分开

php - 加入两个表，求和，并按每月的星期分开

问题描述

我有两个表、预算和事务，如下所示：

预算

|id | acc_code |   desc  |
|:-:|----------|---------|
|1  |A.IV.1    |Car      |
|2  |A.X.3     |Bike     |
|3  |B.I.1     |Airplane |

交易

|id | acc_code | price | transac_date |
|:-:|----------|-------|--------------|
|1  |A.IV.1    |200    | 2021-07-01 |
|2  |A.IV.1    |300    | 2021-07-02 |
|3  |A.X.3     |50     | 2021-07-06 |
|4  |A.IV.1    |250    | 2021-07-06 |
|5  |A.X.3     |75     | 2021-07-12 |
|6  |B.I.1     |4500   | 2021-07-12 |

我需要得到如下查询结果表：

|id | acc_code |   desc  | week 1 | week2 | week 3 | week 4 | week 5 | total of month |
|:-:|----------|---------|--------|-------|--------|--------|--------|-------|
|1  |A.IV.1    |Car      |500     |       |        |        |        | 500 |
|2  |A.X.3     |Bike     |        | 50    |  75    |        |        | 125 |
|3  |B.I.1     |Airplane |        |       | 4500   |        |        | 4500 |

到目前为止，我所做的如下：

// Create connection
<?
  $connect = mysqli_connect($servername, $username, $password, $dbname);
  // Check connection
  if (!$connect) {
   die("Connection failed: " . mysqli_connect_error());
  }

  $sql = "
SELECT
  budget.acc_code,
  budget.desc AS description,
  COALESCE(SUM(transaction.price), 0) AS total
FROM
  budget
LEFT JOIN
  transaction
    ON budget.acc_code = transaction.acc_code
GROUP BY
  budget.desc
";

$query = mysqli_query($connect,$sql) or die (mysqli_error());

?>
<table>
    <thead style="background-color: #33DFFF;" >
        <tr class="info">
          <th>Num</th>
          <th>Acc_code</th>
          <th>Desc</th>
          <th>Total of month</th>            
        </tr>
    </thead>
    <tbody>
        <?php
            $check = mysqli_num_rows($query);
            if($check > 1)
            {
                $num=1;
                while($data = mysqli_fetch_array($query))
                  {                                             
        ?>
                      <tr>
                        <td><?php echo $num; ?></td>
                        <td><?php echo $data['acc_code']; ?></td>
                        <td><?php echo $data['description']; ?></td>
                        <td><?php echo $data['total']; ?></td>
                      </tr> 
        <?php                       
                      $num++;
                  }
             }
        ?>
    </tbody>
</table>
<?
mysqli_close($connect);
?>

结果如下：

|num | acc_code |   desc  | total of month |
|:-:|----------|---------|----------------|
|1  |A.IV.1    |Car      |500 |
|2  |A.X.3     |Bike     |125 |
|3  |B.I.1     |Airplane |4500 |

问题是如何将数据解析为每周并添加每周列。我在做 Join Tables 方面的经验实际上仍然缺乏。

标签： phpmysql

解决方案

在这种情况下可以使用下一个查询：

select
    month(transac_date) mnth,
    acc_code,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 1), price, 0)) week1,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 2), price, 0)) week2,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 3), price, 0)) week3,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 4), price, 0)) week4,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 5), price, 0)) week5,
    sum(price) total_month
from transactions
group by month(transac_date), acc_code;

在这里测试

推荐阅读