gpt4 book ai didi

php - 如何使用 SUM() 和 MAX() 左连接按日期分组的 2 个表

转载 作者:行者123 更新时间:2023-11-29 22:54:06 24 4
gpt4 key购买 nike

我有表 loadhistory(计划“仅选择每个日期的最高值”分组依据并按日期 DESC 排序)

| user_id | customer_id |     date    | bal |
1 1 2015-02-27 500
2 1 2015-02-27 650
3 1 2015-02-28 450
4 1 2015-02-28 620

和表 transactionrecord(并计划使用 SUM(bal) group by 和 order by date DESC 来汇总每个日期的值)

| user_id | customer_id |     date     | bal |
1 1 2015-02-27 50
2 1 2015-02-27 20
3 1 2015-02-28 10

但我想加入这两个表,如下所示:

|    date    |   balance    |   amount paid   |
2015-02-28 620 10
2015-02-27 650 70

我不擅长连接表。这是我到目前为止的代码,但不起作用

$q = "SELECT a.customer_id, SUM(a.bal), a.date, MAX(b.bal) GROUP BY date 
FROM transactionrecord as a
LEFT JOIN loadhistory as b ON b.customer_id = a.customer_id
WHERE customer_id = {$_COOKIE['id']} GROUP BY date
ORDER BY date DESC";
$r = @mysqli_query ($dbc, $q );

echo '<table align="center" cellspacing="0" cellpadding="5" width="45%">
<tr>
<td align="center"><b>Date</b></td>
<td align="center"><b>Balance</b></td>
<td align="center"><b>>Amount Paid></b></td>
</tr>';
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
echo '
<td align="center">' . $row['date'] . '</td>
<td align="center">' . $row['MAX(b.bal)'] . '</td>
<td align="center">' . $row['SUM(a.bal)'] . '</td>

';

在我的查询中要更改什么才能连接包含 SUM() 和 MAX() 的 2 个表?我在 echo 中使用 $row[' '] 正确吗?

非常感谢。

最佳答案

为了达到您想要的结果,您的 SQL 语句应该如下所示:

SELECT a.customer_id, a.date, MAX(COALESCE(b.bal, 0)) AS bal, a.paid
FROM (
SELECT customer_id, date, SUM(bal) AS paid
FROM transactionrecord
GROUP BY customer_id, date
) AS a LEFT JOIN loadhistory AS b
ON a.customer_id = b.customer_id AND a.date = b.date
WHERE a.customer_id = 1
GROUP BY a.customer_id, a.date, a.paid
ORDER BY a.date DESC

并且在您的 php 中,您不能使用 MAX(b.bal) 或 SUM(a.bal) 引用结果列;相反,您必须像我上面所做的那样为列添加别名。因此,您可以将 MAX(b.bal) 称为 bal,将 SUM(a.bal) 称为 paid .

你大部分的 SQL 都是正确的,我只有

  • 删除了错误的 GROUP BY 表达式,
  • 为聚合列(SUMMAX)添加了别名,
  • 将事务余额求和放入子查询中,以防止 loadhistory 表中的多行出现乘法结果
  • 限定所有提及 datecustomer_id 列的内容,因为这些列都出现在两个表中,
  • bal 上添加了 COALESCE,以防左连接导致没有匹配的 loadhistory 记录,
  • 日期上添加了加入条件,因为记录必须具有等效的customer_id日期才能满足您的要求,并且
  • customer_id 添加到 GROUP BY 子句中,因为 SELECT 子句中的任何非聚合字段都应位于 GROUP BY 中 子句可预测结果。

如果您想在表loadhistory中选择每个date具有最高user_id的值,而不是MAX(bal),你需要做这样的事情:

SELECT b.user_id, a.customer_id, a.date,
COALESCE(b.bal, 0) AS bal, SUM(a.bal) AS paid
FROM transactionrecord AS a LEFT JOIN (
SELECT h1.user_id, h1.customer_id, h1.date, h1.bal
FROM loadhistory h1 INNER JOIN (
SELECT MAX(user_id) AS user_id, customer_id, date
FROM loadhistory GROUP BY customer_id, date
) AS h2 ON h1.user_id = h2.user_id
AND h1.customer_id = h2.customer_id
AND h1.date = h2.date
) AS b ON a.customer_id = b.customer_id AND a.date = b.date
WHERE a.customer_id = 1
GROUP BY b.user_id, a.customer_id, a.date, b.bal
ORDER BY a.date DESC

关于php - 如何使用 SUM() 和 MAX() 左连接按日期分组的 2 个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28790771/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com