mysql - 选择 *FROM 表二并从表一计数(column_name)

Question

如何从表二中选择*并从表一中选择计数(t_type)，其中表一和表二中的物种相等

表一 = 树

id | t_type
~~~~~~~~~~~~
1  | Tree one 
2  | Tree two
3  | Tree Three 
4  | Tree Four 
5  | Tree one 

表二 = 请求

id | req_species
~~~~~~~~~~~~
1  | Tree one 
2  | Tree two 
3  | Tree one 
4  | Tree two 

返回的表将具有与表二(请求)相同的行数，在本例中为 4 行。

预期输出

species | Qunatity
~~~~~~~~~~~~
Tree one | 2
Tree two | 1
Tree one | 2
Tree two | 1

Answer 1

一种方法使用相关子查询:

select r.species,
       (select count(*) from trees t where t.species = r.species) as quantity
from requests r;