This question may be silly but I want to know whether it is possible in mysql.
这个问题可能有点傻，但我想知道在mysql中是否可行。
` Course_Table:
“Course_Table:
|--------------|-
|id | name |
----|----------|
|1 |HTML5 |
|2 |CSS3 |
|3 |JavaScript|
|4 |PHP |
|5 |MySQL |
---------------|
User_Table
User_Table
--------------
|id |name |
---------------
|1 |Alice |
|2 |Bob |
|3 |Caroline|
|4 |David |
|5 |Emma |
--------------
user_course
user_course
---------------------------
|id | userid |course|
---------------------------
|1 | 1 | 1 |
|2 | 1 | 2 |
|3 | 1 | 4 |
|4 | 3 | 1 |
|5 | 3 | 5 |
---------------------------`
And I want my result to be like this
我希望我的结果是这样的
` --------------------------
| username | course |
-------------------------|
|Alice | HTML,CSS3,php |
|Caroline | HTML,MYSQL |
--------------------------`
1 个解决方案
#1
4
You need to join the tables first using INNER JOIN since you only want to show users with course.
您需要首先使用内部连接来连接表，因为您只想用course向用户显示。
There is a builtin function in MySQL called GROUP_CONCAT() which concatenates rows.
在MySQL中有一个名为GROUP_CONCAT()的builtin函数，它将行连接起来。
SELECT a.name,
GROUP_CONCAT(c.name) courseList
FROM user_table a
INNER JOIN user_course b
ON a.id = b.userid
INNER JOIN course_table c
ON b.course = c.id
GROUP BY a.name
- SQLFiddle Demo
- SQLFiddle演示
To further gain more knowledge about joins, visit the link below:
要进一步了解关于连接的知识，请访问下面的链接:
- Visual Representation of SQL Joins
- SQL连接的可视化表示
#1
4
You need to join the tables first using INNER JOIN since you only want to show users with course.
您需要首先使用内部连接来连接表，因为您只想用course向用户显示。
There is a builtin function in MySQL called GROUP_CONCAT() which concatenates rows.
在MySQL中有一个名为GROUP_CONCAT()的builtin函数，它将行连接起来。
SELECT a.name,
GROUP_CONCAT(c.name) courseList
FROM user_table a
INNER JOIN user_course b
ON a.id = b.userid
INNER JOIN course_table c
ON b.course = c.id
GROUP BY a.name
- SQLFiddle Demo
- SQLFiddle演示
To further gain more knowledge about joins, visit the link below:
要进一步了解关于连接的知识，请访问下面的链接:
- Visual Representation of SQL Joins
- SQL连接的可视化表示