SQL中的求和子查询

Question

因此，此查询适用于特定的userID。

https://postimg.org/image/iq4jmvblj/

SELECT customer_profile.userID, 
    (SELECT sum(deposit.amountSuccessfulDeposits) 
        FROM customer_profile
        INNER JOIN deposit
            on deposit.userID = customer_profile.userID
        WHERE customer_profile.userID = 2022765) as depositSum,
    (SELECT sum(withdrawal.amountWithdrawal)
        FROM customer_profile
        INNER JOIN withdrawal
            on withdrawal.userID = customer_profile.userID
        WHERE customer_profile.userID = 2022765) as Wsum,
    customer_profile.amountGamingPlayerBalancecurrent as PlayerBalance,
    (sum(deposit.amountSuccessfulDeposits)*0.09) as Psum        

FROM customer_profile INNER JOIN deposit deposit.userID = customer_profile.userID INNER JOIN deposit withdrawal.userID = customer_profile.userID WHERE customer_profile.userID = 2022765 AND withdrawal.withdrawalType = 'Total‘

但是，当我针对整个数据仓库运行时(相同的查询，但去掉了"customer_profile.userID = 2022765“行)，它会对整个数据库中每个userID的depositSum值求和，而不是计算每个单独userID的depositSum值。

我不能在子查询中使用group by，因为它会返回多列。

https://postimg.org/image/3vfy8p20n/

    SELECT customer_profile.userID, 
        (SELECT sum(deposit.amountSuccessfulDeposits)
            FROM customer_profile
            INNER JOIN deposit
                    on deposit.userID = customer_profile.userID
            WHERE customer_profile.userID = deposit.userID) as depositSum,      
        (SELECT sum(withdrawal.amountWithdrawal)
            FROM customer_profile
            INNER JOIN withdrawal
                    on withdrawal.userID = customer_profile.userID
            WHERE customer_profile.userID = withdrawal.userID) as Wsum,
     customer_profile.amountGamingPlayerBalancecurrent as PlayerBalance,
        (sum(deposit.amountSuccessfulDeposits)*0.09) as Psum         
FROM customer_profile 
INNER JOIN deposit  on deposit.userID = customer_profile.userID 
INNER JOIN withdrawal   on withdrawal.userID = customer_profile.userID 
WHERE withdrawal.withdrawalType = 'Total' GROUP BY customer_profile.userID

帮助?

Answer 1

以下是改用correlated subqueries的一种选择：

select cp.userid,
       (select sum(amountsuccessfuldeposits)
        from deposit d
        where cp.userid = d.userid) as depositSum,
       (select sum(amountwithdrawal)
        from withdrawal w
        where withdrawaltype = 'Total' and cp.userid = w.userid) as wsum
from customer_profile cp