mysql - 选择具有多个列的创建结果

我有2个表[user]和[userrights]

[用户]

id      name
1       test1
2       test2

[用户组]

id      name
1       member
1       admin
1       18
2       new
2       16

我想得到这个结果

id      name        group1      group2      group3
1       test1       member      admin       18
2       test2       new         18          null

我尝试了这个，但我认为这是错误的方法

SELECT *,IF(rightname = 'Member', 'Member', NULL) as status
FROM user,userrights
where user.iduser = userrights.iduser

这样我得到这些结果

id      name        rights      member
1       test1       member      member
1       test1       admin       null
1       test1       18          null

我也尝试过

     SELECT user.iduser,GROUP_CONCAT(rightname) as groups FROM user,userrights where user.iduser = userrights.iduser GROUP BY iduser;

所以我得到更好的结果

id      groups
1       member,admin,18
2       new,16

但我想为每个小组增加一个新专栏

最佳答案

如果您确实想要单独的列，则最简单的方法可能是解析group_concat()：

select u.id, u.name,
       substring_index(group_concat(ur.rightname order by ur.rightname), ',', 1) as right1,
       substring_index( substring_index(group_concat(ur.rightname order by ur.rightname), ',', 2), ',', -1) as right2,
       substring_index( substring_index(group_concat(ur.rightname order by ur.rightname), ',', 3), ',', -1) as right3
from user u join
     userrights ur
     on u.iduser = ur.iduser
group by u.id, u.name;

请注意，我还修复了您的过时连接语法并添加了表别名。