php - MYSQL 计数和连接查询

Question

表:用户

Color
1
2
2
2
1
3

表格:颜色

ID      Name
1       Orange
2       Yellow
3       Pink
4       Blue

预期结果

Color      Total
Orange     2
Yellow     3
Pink       1

Mysql查询:

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

如何获得预期结果？请帮我。并提前致谢。

Answer 1

这将帮助您做到这一点。兄弟:)

SELECT C.Name, COUNT(U.Color) as Total from Color C
            LEFT JOIN User U 
            ON
            U.Color = C.ID
            GROUP by C.ID,C.Name

谢谢!