MYSQL从不同的表中添加行和计数

Question

我的 mysql 数据库有两个表。

首先(信息)

+---------+------+----------+
| species | sex  | user     |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        2 |
| snake   | m    |        1 |
+---------+------+----------+

第二(用户)

+--------+-----+
| user   |  id |
+--------+-----+
| amy    | 1   |    
| dav    | 2   |  
| mot    | 3   |       
| mmm    | 4   | 
| aaw    | 5   | 
| dsa    | 6   |  
+--------+-----+

我想为表“users”上的每个用户行计算并显示表“information”中的值像这样

+---------+------+----------+
| user   |  id |  count     |
+---------+------+----------+
| amy    | 1    |        6  |
| dav    | 2    |        2  |
| mot    | 3    |        0  |
| mmm    | 4    |        0  |
| aaw    | 5    |        0  |
| dsa    | 6    |        0  |
+---------+------+----------+

我该如何执行此查询？

Answer 1

select users.user, users.id, count (species.name)
from users left join species 
on users.id = species.user
group by users.user, users.id
order by count (species.name) desc