如何在一个表中添加和减去具有不同condtion的数据

时间：2021-05-30 20:08:36

I have a three tables one is Userregistration,second is withdraw and third is walletbalance from walletbalance table i have to add all credited balance and debited balance and then subtract credited balance from debited balance for actual balance and retrieve data from all the three tables.Below are my table structure.

我有一个三个表,一个是Userregistration,第二个是撤销,第三个是来自walletbalance表的walletbalance我必须添加所有贷记余额和借方余额,然后从借记余额中扣除贷记余额以获得实际余额并从所有三个表中检索数据.Below是我的桌子结构。

Userregistration table

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

withdraw

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

walletbalance

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

I want this output:

我想要这个输出:

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

I have tried this for find actual balance of user but i was unable to ahieve this

我试过这个找到用户的实际余额,但我无法解决这个问题

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'

1 个解决方案

#1

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

#1

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
标签：

相关文章