数据库 权限表 面试题

MySQL的写法：

第一种写法：

select u.name,group_concat(r.name separator ',') rolesname
from user u left join user_role ur on u.id = ur.user_id
left join role r on ur.role_id = r.id
GROUP BY u.name

 第二种写法:

SELECT u.name,group_concat(r.name separator ',') rolesname
FROM user u , user_role ur, role r
WHERE u.id = ur.user_id
and r.id = ur.role_id
group by u.name;

 

Oracle的写法：

SELECT u.name,to_char(wm_concat(r.name)) rolesname
FROM user u , user_role ur, role r
WHERE u.id = ur.user_id
and r.id = ur.role_id
group by u.name;