php - 如何通过日期将左两个SUM()和MAX()连接起来

我有表历史记录（计划按日期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）分组并按日期排序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个表：

|    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表达式，
为汇总（SUM和MAX）列添加了别名，
将事务余额求和放入子查询中，以防止loadhistory表中多行的相乘结果
限定date和customer_id列的所有提及，因为这些列均出现在两个表中，
万一左联接导致没有匹配的COALESCE记录，则在bal上添加了loadhistory，
在date上添加了连接条件，因为记录必须具有等效的customer_id和date才能满足您的要求，并且
将customer_id添加到GROUP BY子句中，因为SELECT子句中的任何未聚合字段都应位于GROUP BY子句中，以实现可预测的结果。


如果要选择表user_id中每个date中每个loadhistory最高的值，而不是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