问题描述
我目前有2张桌子,像这样:
I have currently 2 tables looking like this:
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
我试图拆分用户组,并为每个数字选择用户组,然后将其添加到显示结果中,在最佳情况下应如下所示:
I tried to split the usergroup and select for each number the usergroup and add it to the result of the display, which should look like this in the best case:
uid | name | groups
1 | Michael | member, reporter, admin
2 | Tobias | reporter
3 | Colin | member
到目前为止,我发现最好的结果是从这里:在MySQL中拆分字符串并执行计算但我似乎无法从另一个带有substring_index结果的表中进行选择.我当前的查询如下:
The best result I found so far was from here:Split String and Do Calculation in MySQLbut I can't seem to be able to select from the other table with the substring_index result.My current query looks like this:
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循环用于组数,但结果甚至更糟.
I tried to use a for loop for the number of groups but the results were even worse.
有人暗示如何将选择查询与substring_index结果一起使用吗?
Any hints how I can use the select query with the substring_index result?
推荐答案
您应该考虑规范化.但是,对于当前架构,请考虑以下内容
You should think about the normalization. However for the current schema consider the following
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)
要获得理想的结果,您可以使用以下查询,该查询对于长期运行而言效率不高
To get the desired result you may use the following query which is not efficient for long run
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 |
+------+---------+-----------------------+
现在更好的方法是将关联表创建为
Now a better approach would be to create an association table as
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 |
+------+---------+-----------------------+
这篇关于MySQL拆分字符串并选择结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!