Mysql拆分字符串并选择结果

Question

我目前有两张表如下所示：

frontend_users:
uid | usergroup | name
1   | 1,2,3     | Michael
2   | 2         | Tobias
3   | 1         | Colin
...

和

usergroups:
uid | title
1   | member
2   | reporter
3   | admin

我尝试拆分usergroup并为每个数字选择usergroup并将其添加到显示的结果中，在最好的情况下应该如下所示：

uid | name    | groups
1   | Michael | member, reporter, admin
2   | Tobias  | reporter
3   | Colin   | member

到目前为止，我发现的最好的结果是来自这里：在MySQL中拆分字符串和做计算，但我似乎无法从另一个表中选择substring_index结果。我当前的查询如下：

SELECT frontend_users.uid, frontend_users.name
CASE
    WHEN frontend_users.usergroup LIKE '%,%,%' THEN
        CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 3), ',', -1)))       
    WHEN frontend_users.usergroup LIKE '%,%' THEN
        CONCAT((SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(frontend_users.usergroup, ',', 1)),
            (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(frontend_users.usergroup, ',', 2), ',', -1))),
    ELSE (SELECT usergroups.title FROM usergroups, frontend_users WHERE usergroups.uid = frontend_users.usergroup)
END AS groups
FROM frontend_users, usergroups

我试着用for循环来表示组数，但是结果更糟糕。

对于如何在substring_index结果中使用select查询有任何提示吗？

Answer 1

你应该考虑一下正常化。但是，对于当前模式，请考虑以下几点

mysql> select * from frontend_users ;
+------+-----------+---------+
| uid  | usergroup | name    |
+------+-----------+---------+
|    1 | 1,2,3     | Michael |
|    2 | 2         | Tobias  |
|    3 | 1         | Colin   |
+------+-----------+---------+
3 rows in set (0.00 sec)

mysql> select * from usergroup ;
+------+----------+
| uid  | title    |
+------+----------+
|    1 | member   |
|    2 | reporter |
|    3 | admin    |
+------+----------+
3 rows in set (0.00 sec)

要获得所需的结果，您可以使用下面的查询，该查询在长期运行时是无效的

select 
u.uid,
u.name, 
group_concat(g.title) as groups 
from frontend_users u 
join usergroup g on find_in_set(g.uid,u.usergroup) > 0 
group by u.uid ;

+------+---------+-----------------------+
| uid  | name    | groups                |
+------+---------+-----------------------+
|    1 | Michael | admin,reporter,member |
|    2 | Tobias  | reporter              |
|    3 | Colin   | member                |
+------+---------+-----------------------+

现在，更好的方法是创建一个关联表，如

mysql> create table user_to_group (uid int, gid int);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into user_to_group values (1,1),(1,2),(1,3),(2,2),(3,1);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

一个更好的查询是

select 
u.uid,
u.name, 
group_concat(g.title) as groups 
from frontend_users u 
join user_to_group ug on ug.uid = u.uid 
join usergroup g on g.uid = ug.gid 
group by u.uid ;

+------+---------+-----------------------+
| uid  | name    | groups                |
+------+---------+-----------------------+
|    1 | Michael | member,admin,reporter |
|    2 | Tobias  | reporter              |
|    3 | Colin   | member                |
+------+---------+-----------------------+

Answer 2

的答案是更好的解决方案，但如果您愿意，我只需修复您的最后一段代码。

你可以在小提琴上查看

SELECT frontend_users.uid, frontend_users.name,
CASE
    WHEN usergroup LIKE '%,%,%' THEN
        CONCAT(
           (
             SELECT title FROM usergroups
             WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 1), ',', -1)
           )
          ,',',
           (
              SELECT title FROM usergroups
              WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 2), ',', -1)
            )
          ,',',
           (
              SELECT title FROM usergroups
              WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 3), ',', -1)
            )
          )
    WHEN usergroup LIKE '%,%' THEN
        CONCAT(
           (
             SELECT title FROM usergroups
             WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 1), ',', -1)
           )
          ,',',
           (
              SELECT title FROM usergroups, frontend_users
              WHERE usergroups.uid = SUBSTRING_INDEX(SUBSTRING_INDEX(usergroup, ',', 2), ',', -1) Limit 1
            )
          )
    ELSE
        (SELECT title FROM usergroups, frontend_users WHERE usergroups.uid = usergroup Limit 1)
END AS groups
FROM frontend_users