mysql - 如何将多个子查询与 sum 和 group by 集成

Question

我需要显示用户信息和多个总和，以及它们自己的“位置和分组依据”限定符，这些限定符引用用户的交易余额。我需要显示实际余额、奖金余额和总余额。用一笔钱很容易做到这一点，但我不能用多笔钱做到这一点。

这是我对“实际余额”的查询:

 SELECT v2_user.id, v2_user.username, v2_user.first_name,
 v2_user.last_name,     coalesce(SUM(v2_wallet.amount),0) AS 'Real balance' 
 from v2_wallet
 join v2_user
 on v2_wallet.user_id = v2_user.id
 WHERE TYPE NOT LIKE '%BONUS%'
 group by v2_wallet.user_id

我想在查询中再添加 2 列:

SELECT COALESCE(SUM(v2_wallet.amount),0) AS 'bonus balance' from 
v2_wallet          
WHERE TYPE LIKE '%BONUS%' group by user_id

SELECT COALESCE(SUM(v2_wallet.amount),0) AS 'total balance' from v2_wallet group by user_id

Answer 1

我有一段时间没有做MySql了。但一般来说这段代码应该可以。这是使用 SQL Server sql 语法进行测试的。

SELECT U.UserID,U.FirstName,T.TOTAL,B.BONUS 
FROM Users U 
LEFT JOIN (SELECT USERID, COALESCE (SUM(AMOUNT),0) AS TOTAL
            FROM WALLET
            GROUP BY USERID) AS T ON U.UserID=T.UserId
LEFT JOIN ( SELECT USERID, COALESCE (SUM(AMOUNT),0) AS BONUS
            FROM WALLET
            WHERE TYPE='BONUS'
            GROUP BY USERID) AS B ON U.USERID=B.USERID