php mysql不等于not null_php – mysql不等于不工作

所以我使用以下结构将所有事务存储在事务表中：

+----------------+---------------+------+-----+---------+----------------+

| 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 |

+----------------+---------------+------+-----+---------+----------------+

然后,我将用户拥有的信用总额存储在用户表的“信用”行中.

我试图弄清楚存储在交易表中的每个用户的总额(借方金额信用额)是否与存储在用户表中的信用数量不匹配.

基本上为每个用户

transactions.debit_amount + transactions.credit amount MUST EQUAL user.credits

但是在mysql查询中不等于运算符不起作用(特别是当transactions.total为null时,即该用户的事务表中没有行)：

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)

标签：php,mysql

来源： https://codeday.me/bug/20190626/1292679.html