MySQL跨表计数(*)查询帮助 [英] MySQL Cross-Table Count(*) Query Help

问题描述

SELECT `name` , COUNT(*) AS `count`
FROM `t1`, `t2`
WHERE `t2`.`id` = `t1`.`id`
GROUP BY `t2`.`id`

我想从t1获取名称，并从t2获得ID与t1相同的行数.

I want to obtain the name from t1 and the number of rows in t2 where the id is the same as on t1.

到目前为止，我已经掌握了上述内容，但是如果t2中没有匹配的行，它将不会返回任何数据.如果没有行，我希望count为0(或NULL)，并且名称仍然返回.

I've got the above so far, however it won't return any data if there are no rows in t2 that match. I'd prefer count to be 0 (or NULL) if there are no rows, and the name still returns.

我希望能够按count降序进行排序. (或name ASC)可以吗?

I'd like to be able to sort by the count descending. (or name ASC) is that possible?

推荐答案

这应该对您有用:

SELECT `t1`.`id` , COUNT(`t2`.`id`) AS `count`
FROM `t1` LEFT JOIN `t2` ON `t1`.`id` = `t2`.`id`
GROUP BY `t1`.`id`

左联接确保您拥有t1的所有行，而COUNT(t2.id)使其仅计算t2.id不为null的记录(即-t2中确实存在的记录)

Left join ensures you have all rows from t1, and COUNT(t2.id) makes it count only records where t2.id is not null (that is - those that really exist in t2)

这篇关于MySQL跨表计数(*)查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！