Mysql查询将不受影响变为0。

Question

这是一个简单的查询，在执行时更新一些用户在到期贷款时可用的现金：

$sqlx = "UPDATE competitions
    SET cash = (SELECT cash_after_deduct 
                FROM (SELECT l.competitions_id, 
                             (c.cash-l.due_amount) AS cash_after_deduct
                      FROM loans l
                      JOIN competitions c ON l.competitions_id = c.id
                      WHERE l.due_date='2018-10-28'
                      GROUP BY l.competitions_id) q1
                WHERE q1.competitions_id = competitions.id
    )
";

应修改应在2018-10-28上贷款的用户的现金行。但是，不同到期日的用户的现金行会被重置为0，而他们应该不会受到影响。

知道什么是不对的吗？

在此之前，非常感谢您。

Answer 1

您的UPDATE查询没有条件将更新的范围限制在受due_date影响的范围内。

此外，由于您没有在loans表上使用聚合函数，所以MySQL可以自由地从分组的值中选择任何单个值。这会导致意想不到的结果。

要解决这个问题，可以将SET子查询更改为JOIN。这将防止来自loans表的不匹配记录使用0更新竞赛表。同时减少需要发生的查询数量，因为在SET中使用子查询将要求对每条记录发出查询，以便匹配正在由set更新的竞赛中的当前行。

示例：http://sqlfiddle.com/#!9/2a2c147/1

UPDATE competitions AS c
INNER JOIN (
    SELECT l.competitions_id, SUM(l.due_amount) AS total_due
    FROM loans AS l
    WHERE l.due_date = '2018-10-28'     
    GROUP BY l.competitions_id
    #optionally limit scope to those that have an amount due
    #HAVING total_due > 0
) AS d
ON d.competitions_id = c.id
SET c.cash = c.cash - d.total_due

数据集

competitions
---
| id  | cash | 
|-----|------| 
| 1   | 5.00 | 
| 2   | 2.00 | 
| 3   | 0.00 | 

loans
---
| id | competitions_id | due_amount |   due_date | 
|----|-----------------|------------|------------| 
| 1  |               1 |       1.00 | 2018-10-19 | 
| 2  |               1 |       1.00 | 2018-10-28 | 
| 3  |               2 |       1.00 | 2018-10-28 | 
| 4  |               1 |       1.00 | 2018-10-28 | 
| 5  |               3 |       1.00 | 2018-11-19 | 

结果

| id | cash | total_due | cash_after_deduction | loan_deductions |
|----|------|-----------|----------------------|-----------------|
|  1 |    5 |         2 |                    3 |               2 |
|  2 |    2 |         1 |                    1 |               1 |

这可以通过首先从受competitions_id影响的loans表中检索due_date和due_amount值来实现。

然后，基本的competitions表更新会受到INNER JOIN的限制，它只包含与loans表中找到的记录匹配的记录。

我使用SUM作为聚合函数，以确保competitions_id的所有贷款中的所有due_amount记录都是总计的。这看起来像您所期望的，如果您不想要一个due_amount，则可以修改该查询以匹配您想要的结果。