PostgreSQL交易表多JOIN查询结果远低于预期问题排查求助

• JOIN逻辑存在核心矛盾：你当前的关联规则中，要求d.type=t.type且w.type=t.type，但后续WHERE条件强制d.type='1'、w.type='2'，相当于要求t的type同时等于1和2，该条件不可能满足，INNER JOIN直接过滤了几乎所有数据，这是返回结果偏少的最主要原因。你完全不需要多次JOIN交易表，用条件聚合即可统计存取款总额。
• 余额计算逻辑错误：你当前的统计范围限制为仅2021-09-29单日的交易差值，不是账户历史累计总余额。如果账户的存款大多来自统计日之前，会直接被你的规则漏掉，导致计算出的余额远小于实际值，符合>5000条件的账户自然大幅减少。
• 日期过滤规则校验：首先确认表中date字段的类型为DATE而非字符串，避免样例中DD/MM/YYYY格式的字符串和YYYY-MM-DD格式比较时出现匹配错误。其次你当前所有日期条件都强制等于2021-09-29，如果你的「指定周期」是一段区间而非单日，该规则直接排除了周期内其他日期的交易数据。
• 子查询逻辑验证：单独执行NOT IN内的子查询，确认返回的账户列表是否符合规则，检查是否存在日期边界（比如9月16日当天的交易是否应该被纳入排除范围）、value=0的无效存款被误判的问题。
• 空值处理缺失：如果账户没有取款记录，sum(w.value)会返回NULL，最终sum(d.value)-sum(w.value)的结果也为NULL，不会被计入>5000的结果中，需要用COALESCE函数处理空值。

WITH account_total AS (
    -- 统计指定周期内的账户余额
    SELECT
        t.account,
        a.login,
        SUM(CASE WHEN t.type = 1 THEN t.value ELSE 0 END) AS total_deposit,
        SUM(CASE WHEN t.type = 2 THEN t.value ELSE 0 END) AS total_withdraw,
        SUM(CASE WHEN t.type = 1 THEN t.value ELSE -t.value END) AS balance
    FROM b.transactions t
    INNER JOIN b.accounts a ON t.account = a.id
    -- 替换为你实际需要的余额统计周期起止日期
    WHERE t.date >= '统计周期起始日期' AND t.date <= '2021-09-29'
    GROUP BY t.account, a.login
),
exclude_account AS (
    -- 筛选2021年9月16日之后有有效存款的账户
    SELECT DISTINCT account
    FROM b.transactions
    WHERE type = 1 AND date >= '2021-09-16' AND value > 0
)
SELECT * FROM account_total
WHERE balance > 5000
AND account NOT IN (SELECT account FROM exclude_account)

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