sql - PostgreSQL LEFT JOIN 无法正常工作

Question

我有 2 个表:

T1 (id, flag1)
T2 (id, amount, date, flag2, t1_id);

我有以下查询:

SELECT T1.id, ROUND(COALESCE(SUM(T2.amount), 0), 2) AS spent_amount
FROM T1
LEFT JOIN T2 ON T2.t1_id = T1.id
WHERE T2.date <= '2014-01-01' AND T2.flag2 = 't' AND T1.flag1 = 't'
GROUP BY T1.id

问题是我想在结果中有一行，例如:id = 123, spent_amount = 0 以防我在 T1 中有一个入口，但它在 T2 中没有连接的行。

Answer 1

在您的 T2 上有一个 WHERE 子句，它将过滤掉所有 NULLS:

SELECT T1.id, ROUND(COALESCE(SUM(T2.amount), 0), 2) AS spent_amount 
FROM T1 
LEFT JOIN T2 
    ON T2.t1_id = T1.id 
    AND T2.date <= '2014-01-01' 
    AND T2.flag2 = 't' 
WHERE T1.flag1 = 't'