MySql分隔符拆分查询(一行转多行)
说明
- 适用场景:应用系统根据角色实现菜单权限管控
- 技术点:mysql.help_topic;substring_index()
?
表结构
- user 用户表
- role 角色表
- menu菜单表
SQL实现
SELECT u.u_name, r.r_name, m.m_title FROM user u LEFT JOIN role r ON u.u_role_id = r.r_id JOIN mysql.help_topic h ON h.help_topic_id < (length(r.r_menu_id) - length(REPLACE(r.r_menu_id, ‘,‘, ‘‘)) + 1) LEFT JOIN menu m ON substring_index(substring_index(r.r_menu_id, ‘,‘, h.help_topic_id + 1), ‘,‘,- 1) = m.m_id ORDER BY u.u_id, r.r_id, m.m_id
相关推荐
Noneyes 2020-11-10
此处省略三千字 2020-07-20
银角大王 2020-06-25
阳历六月 2020-06-14
lishaokang 2020-06-14
小惠 2020-06-05
Norsaa 2020-05-07
baobaozai 2020-04-29
fsfsdfsdw 2020-04-21
taisenki 2020-04-08
fenxinzi 2020-03-01
zhiliang 2020-01-31
PHP学习笔记 2020-01-06
hyfcomeon 2013-06-14
PHP学习笔记 2019-12-31
TesterJingel 2019-12-29
silencehgt 2019-12-27
onetozero 2019-12-15