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 的不同之处在于，如果每个成员有多个列表，它将给出更高的上传数量。加入列表后，成员也会有多行，这会增加上传的计数。因此查询 1 可能是错误的。

此外，NULL 值也不计算在内。 手册告知：

COUNT(表达式)

返回行中 expr 非 NULL 值的数量
通过 SELECT 语句检索。结果是一个 BIGINT 值。