JOIN和GROUP_CONCAT与三个表 [英] JOIN and GROUP_CONCAT with three tables

问题描述

我有三个表:

users:        sports:           user_sports:

id | name     id | name         id_user | id_sport | pref
---+--------  ---+------------  --------+----------+------
 1 | Peter     1 | Tennis             1 |        1 |    0
 2 | Alice     2 | Football           1 |        2 |    1
 3 | Bob       3 | Basketball         2 |        3 |    0
                                      3 |        1 |    2
                                      3 |        3 |    1
                                      3 |        2 |    0

表user_sports以优先顺序(pref)链接users和sports.

The table user_sports links users and sports with an order of preference (pref).

我需要进行查询以返回以下内容:

I need to make a query that returns this:

id | name  | sport_ids | sport_names
---+-------+-----------+----------------------------
 1 | Peter | 1,2       | Tennis,Football
 2 | Alice | 3         | Basketball
 3 | Bob   | 2,3,1     | Football,Basketball,Tennis

我已经尝试过使用JOIN和GROUP_CONCAT，但是得到的结果很奇怪.
我需要做一个嵌套查询吗?
有什么想法吗?

I have tried with JOIN and GROUP_CONCAT but I get weird results.
Do I need to do a nested query?
Any ideas?

推荐答案

这不是特别困难.

1. 使用JOIN子句连接三个表.
2. 在您感兴趣的字段上使用Group_concat.
3. 不要忘记未连接的字段上的GROUP BY子句，或者会发生奇怪的事情

SELECT u.id, 
       u.Name, 
       Group_concat(us.id_sport order by pref) sport_ids, 
       Group_concat(s.name order by pref)      sport_names 
FROM   users u 
       LEFT JOIN User_Sports us 
               ON u.id = us.id_user 
       LEFT  JOIN sports s 
               ON US.id_sport = s.id 
GROUP  BY u.id, 
          u.Name 

演示

更新左联接，用于在注释中用户在User_Sports中没有条目的情况

Update LEFT JOIN for when the user doesn't have entries in User_Sports as per comments

这篇关于JOIN和GROUP_CONCAT与三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！