所以我用以下结构将所有事务存储在事务表中:
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| debit_amount | decimal(10,2) | YES | | 0.00 | |
| credit_amount | decimal(10,2) | YES | | 0.00 | |
| flag | int(11) | YES | | NULL | |
| date | datetime | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+----------------+---------------+------+-----+---------+----------------+
然后将用户的积分总额存储在用户表的"credits"行中。
我试图弄清楚是否有一个不匹配的总额(借记金额+贷记金额)为存储在事务表中的每个用户到存储在用户表中的贷记数量。
基本上对于每个用户
transactions.debit_amount + transactions.credit amount MUST EQUAL user.credits
，但不等于操作符在mysql查询不起作用(特别是当事务。Total为空，即在事务表中没有该用户的行):
SELECT s.id AS uid, s.total, s.credits
FROM (
SELECT (sum(t.credit_amount) + sum(t.debit_amount)) AS total, t.userid, u.credits, u.id
FROM transactions AS t
RIGHT JOIN users AS u ON t.userid = u.id
GROUP BY u.id
) AS s
WHERE s.total != s.credits
尝试:
select u.id, u.credits, t.total
from users u
left join (
select userid, sum(coalesce(credit_amount,0)) + sum(coalesce(debit_amount, 0)) as total
from transactions
group by userid
) t on u.id = t.userid
where coalesce(t.total, 0) <> coalesce(u.credits, 0)
你不能在MySQL中比较NULL和非空值(或者至少，如果你这样做，结果总是NULL)。
如果你可以摆脱它，使用INNER JOIN只得到谁做了一个交易的用户。如果没有，则使用COALESCE在没有事务行时提供默认值0，如michaows的回答所示。