如何使用按日期分组的 SUM() 和 MAX() 左连接 2 个表 [英] How to left join 2 tables with SUM() and MAX() grouped by date

问题描述

我有表加载历史(计划仅选择每个日期的最高值"分组并按日期 DESC 排序)

i have table loadhistory (planning to "select only highest value per date" group by and order by date 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

和表事务记录(并计划使用 SUM(bal) group by 和 order by date DESC 对每个日期的值求和)

and table transactionrecord (and planning to sum up values per date using SUM(bal) group by and 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

但我想加入看起来像这样的 2 个表:

But i want to join the 2 tables which would look like this:

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

我不擅长连接表格.到目前为止，这是我的代码，但无法正常工作

im not good in joining tables. this is my code so far and not working

$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[' '] 对吗?

what to change in my query to join the 2 tables with the SUM() and MAX() included? And is my using of $row[' '] in echo right?

非常感谢.

推荐答案

为了达到你想要的结果，你的 SQL 语句应该是这样的:

Your SQL statement should look like this in order to achieve your desired result:

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.

And in your php, you cannot refer to result columns using MAX(b.bal) or SUM(a.bal); instead, you must alias the columns as I've done above. So you can refer to MAX(b.bal) as bal and you can refer to SUM(a.bal) as paid.

你大部分都掌握了 SQL，我只有

You mostly had the SQL right, I only

• 删除了一个错位的 GROUP BY 表达式，
• 为聚合(SUM 和 MAX)列添加别名，
• 将交易余额总和放在子查询中，以防止 loadhistory 表中多行的乘法结果
• 限定所有提及的 date 和 customer_id 列，因为这些列出现在两个表中，
• 在 bal 上添加了 COALESCE，以防左连接导致没有匹配的 loadhistory 记录，
• 在 date 添加了加入条件，因为记录必须具有等效的 customer_id 和 date 以符合您的要求，并且
• 将 customer_id 添加到 GROUP BY 子句中，因为 SELECT 子句中的任何非聚合字段都应在 GROUP BY 中 用于可预测结果的子句.

• removed a misplaced GROUP BY expression,
• added aliases for the aggregated (SUM and MAX) columns,
• put the transaction balance summing in a subquery to prevent multiplicative results for multiple rows in the loadhistory table
• qualified all mentions of date and customer_id columns, since those columns appear in both tables,
• added a COALESCE on bal in case the left join results in no matching loadhistory records,
• added a join criterion on date since records must have equivalent customer_id and date to match your requirements, and
• added customer_id to the GROUP BY clause because any non-aggregated fields in the SELECT clause should be in the GROUP BY clause for predictable results.

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

If you wanted to select the value with highest user_id per date in the table loadhistory instead of MAX(bal), you would need to do something like this:

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

这篇关于如何使用按日期分组的 SUM() 和 MAX() 左连接 2 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！