英文:
SQL query for OneToMany + ManyToMany tables
问题
我有4个表。
用户
| id | 电子邮件 |
|---|---|
| 1 | b@mail.com |
| 2 | a@mail.com |
团队
| id | 标题 | 俱乐部 |
|---|---|---|
| 1 | U7 | 1 |
| 2 | U8 | 1 |
团队用户
| team_id | user_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
俱乐部
| id | 标题 |
|---|---|
| 1 | 曼彻斯特 |
| 2 | 马德里 |
我想编写一个查询，它返回所有用户，仅显示其电子邮件（他们还有其他列），与此用户相关的团队（如果可能的话，以数组的形式），以及一个特定的俱乐部。我希望每个用户只有一行。我希望获取所有团队的所有列。
我还想获取没有相关团队的用户。
这可能吗？
到目前为止，这是我的代码：
SELECT u.email, u.first_name, u.last_name, GROUP_CONCAT(team.title)
FROM user u
LEFT JOIN team_user
ON team_user.user_id = u.id
LEFT OUTER JOIN team
ON team.club_id = u.club_id and team_user.team_id = team.id
GROUP BY email, first_name, last_name
但这只返回以逗号分隔的团队标题。我能否返回其他字段并以数组或对象的方式返回？
英文:
I have 4 tables.
USER
| id | |
|---|---|
| 1 | b@mail.com |
| 2 | a@mail.com |
TEAM
| id | title | club |
|---|---|---|
| 1 | u7 | 1 |
| 2 | U8 | 1 |
TEAM_USER
| team_id | user_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
CLUB
| id | title |
|---|---|
| 1 | manchester |
| 2 | madrid |
I want to write a query which returns all users showing only their email (they have other columns as well) and the teams related to this user(in an array if possible) and a specific club. I want one row per user. I want all columns for the teams.
I want to get back users which do not have related teams as well.
Is that possible?
So far this is my code:
SELECT u.email, u.first_name, u.last_name, GROUP_CONCAT(team.title)
FROM user u
LEFT JOIN team_user
ON team_user.user_id = u.id
LEFT OUTER JOIN team
ON team.club_id = u.club_id and team_user.team_id = team.id
GROUP BY email, first_name, last_name
But this returns only the team title in a comma-separated manner. Can I get back the other fields and in an array or object?
答案1
得分: 1
一种选项使用JSON；我们可以为每个用户生成一个团队对象数组，如下所示：
select u.*,
(
select json_arrayagg(json_object( 'id', t.id, 'title', t.title ) order by t.id )
from teams t
inner join team_user tu on tu.team_id = t.id
where tu.user_id = u.id
) as json_teams
from users u
请注意，这仍然需要你列出团队表中的所有列，因为MySQL不提供将记录转换为JSON对象的原生函数（不像其他数据库如Postgres）。
我不明白表格 `club` 如何与情况有关，因为它似乎与其他表格没有关联。
英文:
One option uses JSON ; we can generate an array of team objects for each user like so:
select u.*,
(
select json_arrayagg(json_object( 'id', t.id, 'title', t.title ) order by t.id )
from teams t
inner join team_user tu on tu.team_id = t.id
where tu.user_id = u.id
) as json_teams
from users u
Note that this still requires you to list all columns in the teams table, since MySQL does not provide a native function to turn a record to json object (unlike other databases such as Postgres).
I don’t see how table club comes into the picture, since it does not seem to relate to other tables.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论