用户表
create table user (
userId int(10) not null,
userName varchar2(255) not null,
primary key (userId)
)
角色表
create table role(
roleId int(10) not null,
roleName varchar2(255) not null, --普通用户,管理员
userId varchar2(255),
perId varchar2(255)
primary key(roleId,perId)
)
权限表
create table permission(
perId int(10) not null,
perName varchar2(255) not null, --增删改查
primary key (perId)
)
select distinct(u.userName) from user u,role r,(select r.roleId,p.perId from role r, permission p
where r.perId = p.perId(+) group by r.roleId,p.perId having( count(p.perId)>= 3 )) d
where r.roleId = d.roleId
and u.userId = r.userId