mysql 读多个表 如何快_复杂的MySQL查询与多个表(阅读更多细节)

我有4个表格：团队，用户，用户团队和权限：

用户：

UserID | Name

------------------------

1 | John

2 | Mike

3 | Tom团队：

TeamID | Name

-------------------------------

1 | FC Barcelona

2 | Real Madrid

3 | Manchester United

4 | Liverpool权限：

PermissionsID | Name

-------------------------------

1 | Read Comments

2 | Write Comments

3 | Send Email

4 | AdminUserTeams：

ID | UserID | TeamID | PermissionID

--------------------------------------------

1 | 1 | 1 | 1

2 | 1 | 1 | 2

3 | 1 | 1 | 4

4 | 1 | 2 | 1

4 | 1 | 4 | 3

- H2>

- H2>

UserID = 1(我们知道这在查询之前)

我想让查询得到像这样的东西：

Permission.Name | FC Barcelona | Real Madrid | Manchester United | Liverpool | etc...

----------------------------------------------------------------------------

Read Comments | (something) | (something) | NULL | NULL

Write Comments | (something) | NULL | NULL | NULL

Send Email | NULL | NULL | NULL | (something)

Admin | (something) | NULL | NULL | NULL球队数量不限。

有任何想法吗？如果有多个查询，我不介意...

提前致谢！

解

仅限首选队伍：

SELECT TeamID, name FROM Teams然后用团队数据创建一个新的查询：

SELECT

Permissions.name as 'permissionName',

MAX(CASE WHEN Teams.name = 'FC Barcelona' THEN Teams.name ELSE NULL END) AS 'FC Barcelona'

FROM Permissions

LEFT JOIN UserTeams ON UserTeams.PermissionID = Permissions.PermissionID AND UserTeams.UserID = '1'

LEFT JOIN Teams ON Teams.TeamID = UserTeams.TeamID

GROUP BY Permissions.name现在我们得到了我们想要的结果。