mysql - 连接两个表mysql查询返回所有用户列表值？

Question

连接两个表的 MySQL 查询返回所有用户列表值。请更正此查询或提供一些查询。

表1:用户

+---------+------------+-----------+
| user_id | user_name  | cource_id |
+---------+------------+-----------+
|       1 | ramalingam | 1,2,3,4   |
|       2 | yuvi       | 1         |
|       3 | Saravanan  | 1,2,3     |
|       4 | gandhi     | 1         |
+---------+------------+-----------+

表2:类(class)

+-----------+-------------+
| cource_id | cource_name |
+-----------+-------------+
|         1 | php         |
|         2 | wordpress   |
|         3 | seo         |
|         4 | magento     |
+-----------+-------------+

输出

--------------------------------------
user_id | user_name | cource_id
--------------------------------------
1       | ramalingam| php,wordpress,seo,magnto
2       | yuvi      | php
3       | Saravanan | php,wordpress,seo
4       | gandhi    | php

这是我的查询

SELECT u.user_id,u.user_name, GROUP_CONCAT(c.cource_name)as course_name
FROM users as u 
LEFT JOIN course as c ON c.cource_id = u.user_id

感谢您为我提供的任何帮助...

Answer 1

总的来说，数据库设计很糟糕，根本不要使用逗号分隔的列表。然而，您应该在 JOIN 子句中使用 FIND_IN_SET() 来实现此目的:

SELECT
    u.user_id,
    u.user_name,
    GROUP_CONCAT(c.cource_name) AS course_name
FROM
    users AS u
LEFT JOIN cource AS c ON FIND_IN_SET(c.cource_id, u.cource_id)
GROUP BY
    u.user_id,
    u.user_name

输出是:

+---------+------------+---------------------------+
| user_id | user_name  | course_name               |
+---------+------------+---------------------------+
|       1 | ramalingam | php,wordpress,seo,magento |
|       2 | yuvi       | php                       |
|       3 | Saravanan  | php,wordpress,seo         |
|       4 | gandhi     | php                       |
+---------+------------+---------------------------+
4 rows in set