mysql - 使用 "join"和 "group by"避免子查询

Question

我试图避免子查询，但无法获得正确的结果:

这是原始查询:

SELECT T1.IdL, T1.REG, T1.YearIn, T1.URL,
(SELECT Count(*) FROM T2 WHERE T1.IdL = T2.IdL) AS IdL_Count
FROM T1

以下尝试避免子查询，但不起作用，因为 T2 中没有记录的行丢失了

SELECT T1.IdL, T1.REG, T1.YearIn, T1.URL, Count(*) AS IdL_Count
FROM T1 INNER JOIN T2 USING(IdL)
GROUP BY IdL

所以我尝试了 LEFT JOIN 但我得到了错误的 IdL_Count: 1 而不是 0

有没有办法避免子查询？

Answer 1

没有办法避免子查询，至少没有指定的表结构(索引等)。但这个查询应该表现得更好

SELECT T1.IdL, T1.REG, T1.YearIn, T1.URL, coalesce(T3.count, 0) AS IdL_Count
FROM T1
LEFT JOIN (SELECT IdL, count(*) as count FROM T2 GROUP BY IdL) T3 on T3.IdL = T1.IdL