PostgreSQL的LEFT JOIN未正常工作

Question

I have 2 tables:

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

问题在于，如果我在T1中有一条记录，但它在T2中没有相关联的记录，那么我希望结果中有一行类似于：id = 123, spent_amount = 0。请注意保留HTML标签。

- uno_ordinary

---

我不了解postgresql，但在SQL Server中，您需要将限制T2的条件从where子句移动到join子句中：LEFT JOIN T2 ON T2.t1_id = T1.id AND T2.date <= '2014-01-01' AND T2.flag2 = 't' WHERE T1.flag1 = 't' - Giannis Paraskevopoulos

“WHERE”子句否定了“LEFT JOIN”，因为它排除了任何“NULL”值。要么将其移入“ON”子句中，要么在“WHERE”子句上使用“COALESCE”。 - Jaloopa

Answer 1

在T2上使用WHERE子句，它会过滤掉所有的NULL值：

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'

- Giannis Paraskevopoulos