预期结果不是来自mysql内部联接

Question

我有以下两个表格：

table: loan
--------------------------------------------------------
 id | advance_id | loan_amount | deposit_amount | date
--------------------------------------------------------
 1 | 22556678 | 5000 | 0 | 2015-02-06
--------------------------------------------------------

table: advance
--------------------------------------------------------
id | advance_id | advance_amount | purpose | date
--------------------------------------------------------
 1 | 22556678 | 20000 | purchase | 2015-01-30
 2 | 22556678 | 10000 | purchase | 2015-01-31
--------------------------------------------------------

我的代码是：

SELECT a.advance_id, sum(a.advance_amount) as adv_amount, a.purpose, a.date, sum(l.loan_amount) as loan_amount FROM advance as a INNER JOIN loan as l ON a.advance_id=l.advance_id GROUP BY a.advance_id HAVING sum(l.loan_amount)-sum(l.deposit_amount)>0

说明：表“预付款”为预付款表，“贷款”为贷款账户表。预付款可用于同一目的多次（此处用于购买产品）。每个预付款由预付款id标识。由于两个预付款用于相同的目的（此处用于购买产品），它们将具有相同的预付款id。另一方面，贷款是针对任何预付款进行的。贷款也可以采取多次对同一预付款。如果要偿还任何贷款，则会将其插入预付款id的“存款金额”列中。

在loan_page.php，只有当任何贷款未支付/未调整时，才会显示贷款。为了识别它，我必须通过以下代码计算贷款和存款列对相同的预先标识：

GROUP BY a.advance_id HAVING sum(l.loan_amount)-sum(l.deposit_amount)>0

然后，如果任何贷款未付，我必须确定每advance_id有多少贷款。所以我必须选择以下内容：

sum(l.loan_amount)

我获取的输出将是：

echo 'Total loan Amount: '.$row['loan_amount'].'<br />';
echo 'Advance ID: '.$row['advance_id'].'<br />';
echo 'Advance Amount: '.$row['adv_amount'].'<br />';
echo 'Purpose of Advance: '.$row['purpose'];

上述代码很有效，但总贷款额将增加一倍（本例中为10000），尽管将为5000。我认为我的mysql查询中的GROUPBY或HAVING子句有错误。

请让我知道，如何正确的代码，使总和（l.loan_amount）将是5000在这种情况下，而不是10000。

Answer 1

您希望使用联合所有而不是连接将表组合在一起。或者，在连接之前聚合。

下面是联合所有版本：

SELECT advance_id,
       sum(advance_amount) as adv_amount,
       max(purpose) as purpose,
       min(date) as date,
       sum(loan_amount) as loan_amount
FROM ((SELECT a.advance_id,
              a.advance_amount,
              a.purpose,
              a.date,
              NULL as loan_amount,
              NULL as deposit_amount
       FROM advance
      ) UNION ALL
      (SELECT l.advance_id, NULL, NULL, l.date, loan_amount, deposit_amount
       FROM loan l
      )
     ) al
GROUP BY advance_id
HAVING sum(loan_amount) - sum(deposit_amount) > 0;

Answer 2

loan_amount是双重的原因是因为您正在使用advance_id在表之间进行内连接。由于advance_id在advance_table中出现两次，因此贷款行将出现两次。在视觉上，连接会给你这样的东西：

| advance | loan |
|    1    |  1   |
|    2    |  1   |

因此，当您对l.loan_金额进行聚合时，它将被计算两次。为了解决这个问题，我将单独对每个表进行聚合，然后将它们连接在一起。例如，只需从贷款表中单独获取预付款id和金额（贷款金额），如下所示：

SELECT advance_id, SUM(loan_amount)
FROM loan
GROUP BY advance_id;

我注意到您原来的中有子句检查贷款金额-存款金额，您可以在上面的查询中执行该条件：

SELECT advance_id, SUM(loan_amount) AS totalLoan
FROM loan
GROUP BY advance_id
HAVING SUM(loan_amount) - SUM(deposit_amount) > 0;

接下来，在预付表中预先形成必要的聚合，如下所示：

SELECT advance_id, SUM(advance_amount) AS totalAdvance
FROM advance
GROUP BY advance_id;

然后，我将连接这两个表以获得所需的值：

SELECT t1.advance_id, t1.totalAdvance, t2.totalLoan
FROM(
  SELECT advance_id, SUM(advance_amount) AS totalAdvance
  FROM advance
  GROUP BY advance_id) t1
JOIN(
  SELECT advance_id, SUM(loan_amount) AS totalLoan
  FROM loan
  GROUP BY advance_id
  HAVING SUM(loan_amount) - SUM(deposit_amount) > 0) t2 ON t2.advance_id = t1.advance_id;

笔记

我没有包括目的或日期，因为它们在预付款表中不一致。你必须考虑每一种情况。如果两排的目的不同呢？日期不同，所以如果有，您需要返回哪个日期？一旦知道了这一点，就可以将它们添加到此查询中，很可能添加到“advance”子查询中。

下面是一个SQLFiddle示例。