如何在MySQL中通过Union与左连接实现存提款数据统计查询

核心思路 #

先分别统计指定日期范围内、状态为Approved的用户存款/取款总额，再通过关联所有有有效交易记录的用户，用空值处理函数覆盖单边无记录的场景，最后计算净额并排序。

假设表结构 #

• 存款表（deposits）：userid, name, email, amount, status, create_date
• 取款表（withdrawals）：userid, name, email, amount, status, create_date

查询语句 #

WITH all_valid_users AS (
    -- 提取所有有有效交易记录的用户（去重）
    SELECT userid, name, email
    FROM deposits
    WHERE status = 'Approved' 
      AND create_date BETWEEN '2024-01-01' AND '2024-06-30'
    UNION
    SELECT userid, name, email
    FROM withdrawals
    WHERE status = 'Approved' 
      AND create_date BETWEEN '2024-01-01' AND '2024-06-30'
),
deposit_summary AS (
    -- 统计用户总存款
    SELECT userid, SUM(amount) AS total_deposit
    FROM deposits
    WHERE status = 'Approved' 
      AND create_date BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY userid
),
withdrawal_summary AS (
    -- 统计用户总取款
    SELECT userid, SUM(amount) AS total_withdrawal
    FROM withdrawals
    WHERE status = 'Approved' 
      AND create_date BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY userid
)
SELECT 
    au.userid,
    au.name,
    au.email,
    COALESCE(ds.total_deposit, 0) AS 总存款,
    COALESCE(ws.total_withdrawal, 0) AS 总取款,
    COALESCE(ds.total_deposit, 0) - COALESCE(ws.total_withdrawal, 0) AS 净额
FROM all_valid_users au
LEFT JOIN deposit_summary ds ON au.userid = ds.userid
LEFT JOIN withdrawal_summary ws ON au.userid = ws.userid
ORDER BY 净额 DESC;

关键细节说明 #

1. UNION获取全量用户：避免遗漏仅存存款或仅存取款记录的用户，同时自动去重
2. COALESCE处理空值：当用户无对应交易记录时，将总额设为0，保证净额计算正确
3. 日期参数替换：把示例中的'2024-01-01'和'2024-06-30'替换为实际业务需要的日期区间
4. CTE拆分逻辑：将用户提取、交易统计拆分为独立模块，SQL结构更清晰，后期维护更方便

内容的提问来源于stack exchange，提问作者BestSolutionsfinder