如何关联budget与transaction表按周分组求和并新增周统计列

一、核心逻辑说明 #

你需要用条件聚合的方式统计每周金额，核心是先计算每笔交易属于当月的第几周，再按周分类求和即可，不需要额外关联表。

二、SQL语句修改 #

把你原来的SQL替换成下面的内容：

SELECT
  budget.id,
  budget.acc_code,
  budget.desc AS description,
  -- 统计第1周金额
  COALESCE(SUM(CASE WHEN WEEK(transaction.transac_date,1) - WEEK(DATE_SUB(transaction.transac_date, INTERVAL DAYOFMONTH(transaction.transac_date)-1 DAY),1) +1 = 1 THEN transaction.price ELSE 0 END), 0) AS week1,
  -- 统计第2周金额
  COALESCE(SUM(CASE WHEN WEEK(transaction.transac_date,1) - WEEK(DATE_SUB(transaction.transac_date, INTERVAL DAYOFMONTH(transaction.transac_date)-1 DAY),1) +1 = 2 THEN transaction.price ELSE 0 END), 0) AS week2,
  -- 统计第3周金额
  COALESCE(SUM(CASE WHEN WEEK(transaction.transac_date,1) - WEEK(DATE_SUB(transaction.transac_date, INTERVAL DAYOFMONTH(transaction.transac_date)-1 DAY),1) +1 = 3 THEN transaction.price ELSE 0 END), 0) AS week3,
  -- 统计第4周金额
  COALESCE(SUM(CASE WHEN WEEK(transaction.transac_date,1) - WEEK(DATE_SUB(transaction.transac_date, INTERVAL DAYOFMONTH(transaction.transac_date)-1 DAY),1) +1 = 4 THEN transaction.price ELSE 0 END), 0) AS week4,
  -- 统计第5周金额
  COALESCE(SUM(CASE WHEN WEEK(transaction.transac_date,1) - WEEK(DATE_SUB(transaction.transac_date, INTERVAL DAYOFMONTH(transaction.transac_date)-1 DAY),1) +1 = 5 THEN transaction.price ELSE 0 END), 0) AS week5,
  -- 月度总金额
  COALESCE(SUM(transaction.price), 0) AS total
FROM
  budget
LEFT JOIN
  `transaction`
ON budget.acc_code = `transaction`.acc_code
-- 可以加条件指定统计月份，比如统计2021年7月就打开下面的注释
-- WHERE YEAR(`transaction`.transac_date) = 2021 AND MONTH(`transaction`.transac_date) =7
GROUP BY
  budget.id, budget.acc_code, budget.desc
ORDER BY budget.id ASC

说明：周计算逻辑默认周一为一周起始，如果你需要周日为起始，把WEEK函数的第二个参数从1改成0即可；transaction是MySQL关键字，所以加反引号包裹避免语法错误。

三、前端HTML/PHP部分修改 #

调整表头和行输出的结构即可：

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

$sql = "把上面修改后的SQL语句直接替换到这里即可";

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

?>
<table>
    <thead style="background-color: #33DFFF;" >
        <tr class="info">
          <th>Num</th>
          <th>Acc_code</th>
          <th>Desc</th>
          <th>Week 1</th>
          <th>Week 2</th>
          <th>Week 3</th>
          <th>Week 4</th>
          <th>Week 5</th>
          <th>Total of month</th>            
        </tr>
    </thead>
    <tbody>
        <?php
            $check = mysqli_num_rows($query);
            // 原来的>1改成>0，避免只有1条数据时不显示
            if($check > 0)
            {
                $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>
                        <!-- 周金额为空时显示空白，不需要0就用三元运算符判断 -->
                        <td><?php echo $data['week1'] >0 ? $data['week1'] : ''; ?></td>
                        <td><?php echo $data['week2'] >0 ? $data['week2'] : ''; ?></td>
                        <td><?php echo $data['week3'] >0 ? $data['week3'] : ''; ?></td>
                        <td><?php echo $data['week4'] >0 ? $data['week4'] : ''; ?></td>
                        <td><?php echo $data['week5'] >0 ? $data['week5'] : ''; ?></td>
                        <td><?php echo $data['total']; ?></td>
                      </tr> 
        <?php                       
                      $num++;
                  }
             }
        ?>
    </tbody>
</table>
<?php
mysqli_close($connect);
?>

四、优化建议 #

• 如果需要切换统计月份，只需要修改SQL里的WHERE条件即可，周统计逻辑会自动适配对应月份的周次
• 建议避免使用MySQL短标签<?，统一用<?php兼容性更好
• GROUP BY时用budget.id作为分组依据更严谨，避免出现科目描述重名时统计错误

内容的提问来源于stack exchange，提问作者Jymo