如何从现有数据创建新的逗号分隔列? [英] How to create new comma-separated column from existing data?
问题描述
我们有以下 account
表(MySQL RDBMS):
We have the following account
table (MySQL RDBMS):
+----+-------------------+----------------+-----------------+-----------------+
| id | username | admin_for_blog | editor_for_blog | author_for_blog |
+----+-------------------+----------------+-----------------+-----------------+
| 1 | author_only | NULL | NULL | 1 |
| 2 | editor_and_author | NULL | 2 | 2 |
| 3 | admin_only | 3 | NULL | NULL |
+----+-------------------+----------------+-----------------+-----------------+
我们正在迁移到基于角色的授权,因此我们需要为上述现有数据找到新的角色.角色是:AUTHOR
、EDITOR
和 ADMIN
.一个用户可以链接到多个角色.角色将存储为逗号分隔值(顺序无关紧要).在上面的数据中,输出应该是:
We are migrating to a role-based authorization, so we need to find the new roles for the above existing data. Roles are: AUTHOR
, EDITOR
and ADMIN
. A user can be linked to multiple roles. Roles will be stored as comma-separated values (order doesn't matter). In the above data, the output should then be:
+----+-------------------+---------------+----------------+-----------------+-----------------+
| id | username | roles | admin_for_blog | editor_for_blog | author_for_blog |
+----+-------------------+---------------+----------------+-----------------+-----------------+
| 1 | author_only | AUTHOR | NULL | NULL | 1 |
| 2 | editor_and_author | AUTHOR,EDITOR | NULL | 2 | 2 |
| 3 | admin_only | ADMIN | 3 | NULL | NULL |
+----+-------------------+---------------+----------------+-----------------+-----------------+
所以基本上:
- 如果
author_for_blog
不为空,则添加角色AUTHOR
- 如果
editor_for_blog
不为空,则添加角色EDITOR
- 如果
admin_for_blog
不为空,则添加角色ADMIN
- if
author_for_blog
is not null, then add roleAUTHOR
- if
editor_for_blog
is not null, then add roleEDITOR
- if
admin_for_blog
is not null, then add roleADMIN
关于如何做到这一点的任何提示?我已经能够为每个用户附加一个角色,但我找不到一种方法来管理以逗号分隔的多个角色:
Any hint on how to to this? I've been able to attach one role per user, but I can't find a way to manage multiple roles separated by commas:
select username,
case admin_for_blog is not null when true then 'ADMIN' else
case editor_for_blog is not null when true then 'EDITOR' else
case author_for_blog is not null when true then 'AUTHOR' else
'error!' end end end
from account
推荐答案
您可以使用函数 concat_ws
跳过 null
值.
You can use the function concat_ws
which skips the null
values.
select username,
concat_ws(','
,case when admin_for_blog is not null then 'ADMIN' end
,case when editor_for_blog is not null then 'EDITOR' end
,case when author_for_blog is not null then 'AUTHOR' end
)
from account
这篇关于如何从现有数据创建新的逗号分隔列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!