MYSQL计数和连接查询

Question

Table: User

Color
1
2
2
2
1
3

Table: Color

ID      Name
1       Orange
2       Yellow
3       Pink
4       Blue

Expected Result

Color      Total
Orange     2
Yellow     3
Pink       1

Mysql Query:

SELECT * FROM User LEFT JOIN Color ON Color.ID = User.Color;

How can I get my expected result? Please help me. and thanks in advance.

Answer 1

SELECT c.NAME, COUNT(*) AS Total 
FROM Color c
INNER JOIN User u ON (u.Color = c.Id)
GROUP BY c.NAME;