我们如何在mysql中使用JOIN设置计数

Question

table1

p_id    pc_id
3   66
4   67
5   66
6   68
7   65
8   68

table2

pc_id   pc_name
66  AB
67  Cd
65  Ef
68  Gh

I expect in result

Name    Count
AB  2
Cd  1
Ef  1
Gh  2   

I am using my code

SELECT COUNT(*), table2.pc_name
from table2
inner JOIN table1
ON
table2.pc_id = table1.p_id

Answer 1

You need not only to join tables, but to group the data by PC name as well:

SELECT table2.pc_name as PC_Name, COUNT(table1.p_id) as PC_Count
FROM table2 inner JOIN table1 
    ON table2.pc_id = table1.pc_id
GROUP BY table2.pc_name

Here is a link to SQL Fiddle

The above query is working assuming that PC name is unique. If not - you need to also use PC ID in SELECT and GROUP BY statements