首先，准备一个数据集，它是两个表的UNION，但只包括“Approved”记录：

SELECT *, 'deposit' AS type FROM deposit WHERE status = 'Approved'
UNION ALL
SELECT *, 'withdraw' AS type FROM withdraw WHERE status = 'Approved'

字符串
然后你可以从这个数据集中选择，使用一些条件和：

WITH alltypes AS (
    SELECT *, 'deposit' AS type FROM deposit WHERE status = 'Approved'
    UNION ALL
    SELECT *, 'withdraw' AS type FROM withdraw WHERE status = 'Approved'
)
SELECT
    userid,
    name,
    email,
    SUM(if(type = 'deposit', amount, 0)) AS 'total deposit',
    SUM(if(type = 'withdraw', amount, 0)) AS 'total withdraw',
    SUM(if(type = 'deposit', amount, -1 * amount)) AS 'net'
FROM alltypes
GROUP BY userid, name, email

型
这给出了：

+--------+------+---------------+---------------+----------------+------+
| userid | name | email         | total deposit | total withdraw | net  |
+--------+------+---------------+---------------+----------------+------+
|      1 | fine | [email protected] |           500 |             50 |  450 |
|      4 | new  | [email protected]   |            20 |             30 |  -10 |
+--------+------+---------------+---------------+----------------+------+

型