mysql - 关联表左连接

Question

我在尝试连接使用关联表的表时需要帮助。

我有一个用户表

Users Table
user_id
user_name

Association Table
user_id
project_id

Project Table
project_id
project_name

我需要拉取一个用户和他们关联的项目数。

SELECT u.user_name, COUNT(p.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
GROUP BY u.user_name

如何关联两个表？

Answer 1

如果你想关联项目和用户，你需要做2个连接:

SELECT u.user_name, COUNT(p.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
LEFT JOIN projects p ON p.project_id = a.project_id
GROUP BY u.user_name

如果你想让它更快，你可以这样做:

SELECT u.user_name, COUNT(a.project_id) projects
FROM users u
LEFT JOIN association a ON u.user_id = a.user_id
GROUP BY u.user_name