sql - mysql - 通过 1 个查询从 2 个表获取详细信息

Question

我有 2 个 mysql 表:

第一个表table1:

| ID | NAME   |
| 1  | cat    |
| 2  | mouse  |
| 3  | mouse  |
| 4  | mouse  |
| 5  | mouse  |
| 6  | mouse  |

第二个表table2:

| ID | NAME_NA   |
| 1  | cat       |
| 2  | mouse     |
| 3  | cd_rom    |
| 4  | dvd_rw    |
| 5  | house     |

我想输出这个:

mouse    -   5 entries
cat      -   1 entry
cd_rom   -   0 entries
dvd_rw   -   0 entries
house    -   0 entries

Answer 1

Use a LEFT JOIN :

   SELECT x.name_na AS name,
          COALESCE(COUNT(y.name), 0) AS num_entries
     FROM TABLE2 x
LEFT JOIN TABLE1 y ON y.name = x.name_na

...获取:

name     num_entries
--------------------
mouse    5 
cat      1
cd_rom   0
dvd_rw   0
house    0