php - 如何从具有不同where条件的表中添加和减去数据

Question

我有三个表，一个是 Userregistration，第二个是 withdraw，第三个是 walletbalance 表中的 walletbalance 我必须添加所有贷方余额和借方余额，然后从借方余额中减去贷方余额以获得实际余额并从所有三个表中检索数据表。下面是我的表结构。

用户注册表

........................................
id      fullname   mobile_no       email
.........................................
5      varun         12344567   abc@gmail
6      nitin         12345678   def@gmail

退出

...............................
wid   userid    withdraw_status
...............................
1     5         pending
2     6         pending

钱包余额

..........................................
id   user_id   balance  transaction_type
..........................................
1      5       100        credit
2      5       20         debit
3      6       200        credit
4      6       100        debit

我想要这个输出:

.................................................................
wid  user_id balance  withdraw_status  fullname  mobile_no  email
.................................................................
1     5        80       pending        varun     12344567   abc@gmail
2     6        100      pending        nitin     12344567   def@gmail

我试过这个来找到用户的实际余额，但我无法实现这个

SELECT SUM(`balance`) as b1 from walletbalance WHERE `user_id`='5' and `transaction_type`='credit' UNION SELECT SUM(`balance`) as b2 from walletbalance WHERE `user_id`='5' and `transaction_type`='debit'

Answer 1

SELECT t2.wid, t2.userid AS user_id, t3.balance, t2.withdraw_status,
    t1.fullname, t1.mobile_no, t1.email    
FROM Userregistration t1
INNER JOIN withdraw t2
    ON t1.id = t2.userid
INNER JOIN
(
    SELECT user_id,
        SUM(CASE WHEN transaction_type = 'credit'
                 THEN balance
                 ELSE balance * -1
            END) AS balance
    FROM walletbalance
    GROUP BY user_id
) t3
    ON t1.id = t3.user_id