MySQL 左连接计数

Question

我有一个表的左连接，并希望在按父表的列分组后计算其中的列数:

SELECT * , COUNT(list.id) AS listcount, COUNT(uploads.id) AS uploadcount 
FROM members 
LEFT JOIN lists ON members.id= list.mid
LEFT JOIN uploads ON members.id= uploads.mid
GROUP BY members.id

假设用户可以根据用户类型拥有列表或上传。那么上面的查询是否足够好？如果不是，为什么？

还是我必须使用这个查询？

SELECT * , l.listcount,   u.uploadcount 
FROM members 
LEFT JOIN (select count(lists.id) as listscount,mid from lists group by mid) as l
                                                                on l.mid = m.id
LEFT JOIN (select count(uploads.id) as uploadscount
                           ,mid from uploads group by mid) as u on u.mid = m.id
GROUP BY members.id

还是关联子查询？

SELECT *,
      (select count(lists.id) as listscount from lists as l where l.mid = m.id
       group by mid) as listcount
      (select count(uploads.id) from uploads as u where u.mid = m.id
       group by mid) as uploadscount 
FROM members 
GROUP BY members.id

哪个是最好的解决方案？

Answer 1

查询 2 和 3 中缺少成员的别名 m。否则他们应该给出相同的数字。

查询 2(固定)执行速度最快。

查询 1 的不同之处在于，如果每个 成员 有多个 lists，它将为 uploads 提供更高的数字。加入lists后，一个成员也会有多个行，这会增加uploads的计数。所以查询 1 可能是错误的。

此外，NULL 值不被计算在内。 manual informs :

COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.