我有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
。
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'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句