轻松掌握用户，角色，功能之间的关系视图

create table userinfo(id varchar(20),name varchar(20)); create table roleinfo(id varchar(20),name varchar(20)); create table functioninfo(id varchar(20),name varchar(20)); create table userrole(userid varchar(20),roleid varchar(20)); create table rolefunction(roleid varchar(20),functionid varchar(20)); insert into userinfo values("1","gao1"); insert into userinfo values("2","gao2"); insert into userinfo values("3","gao3"); insert into roleinfo values("1","role1"); insert into roleinfo values("2","role2"); insert into roleinfo values("3","role3"); insert into functioninfo values("1","function1"); insert into functioninfo values("2","function2"); insert into functioninfo values("3","function3"); insert into userrole values("1","1"); insert into userrole values("1","2"); insert into userrole values("2","2"); insert into userrole values("3","1"); insert into userrole values("3","2"); insert into userrole values("3","3"); insert into role function values("1","1"); insert into role function values("1","2"); insert into role function values("2","2"); insert into role function values("3","1"); insert into role function values("3","2"); insert into role function values("3","3");

select u.name as username,r.name as rolename,f.name as functionname from userrole ur inner join userinfo u on ur.userid=u.id inner join roleinfo r on ur.roleid=r.id inner join rolefunction rf on rf.roleid=r.id inner join functioninfo f on rf.functionid=f.id order by u.name;