MySQL列分割成多行的方法
原数据
| id |
menu_name |
parent_id |
ancestors |
order_num |
status |
| 7 |
333 |
0 |
0 |
3 |
0 |
| 8 |
4444 |
0 |
0 |
4 |
1 |
| 9 |
40001 |
8 |
0,8 |
1 |
1 |
| 10 |
4000102 |
9 |
0,8,9 |
1 |
1 |
将表中的'ancestors'字段的字符串以逗号分隔成行
select
SUBSTRING_INDEX( SUBSTRING_INDEX( ancestors, ',', b.help_topic_id + 1 ), ',', -1 ) as coop_branch
from
dorm_group as a
join mysql.help_topic as b on
b.help_topic_id < ( length(a.ancestors) - length( replace ( a.ancestors, ',', '' ) ) + 1 )
where a .id = 10
处理结果
MySQL多行合并成列
原数据
| id |
menu_name |
parent_id |
ancestors |
| 8 |
4444 |
0 |
0 |
| 9 |
40001 |
8 |
0,8 |
将表中的'menu_name'字段的字符串以逗号分隔合并
select group_concat(dg.menu_name) from `foen-vue`.dorm_group dg
where FIND_IN_SET(dg.id ,(select ancestors from `foen-vue`.dorm_group where id = 10)) > 0
处理结果
| group_concat(dg.menu_name) |
|---|
| 4444,40001 |
