php – mysql不等于不工作

发布时间：2020-12-13 17:10:19 所属栏目：PHP教程来源：网络整理

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

+----------------+---------------+------+-----+---------+----------------+
| 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,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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!