用户表 Tb_Users
角色表 TbRole
用户角色关系表 User_Role_Rela
现已经建立此连:
create or replace view USER_ROLE_RELA_VIEW as
select t1.user_id,t1.user_name,t1.mobile,t2.tb_role_id,t2.rela_id from Tb_Users t1 left outer join User_Role_Rela t2 on t1.user_id=t2.user_id请问:
如何通过一条SQL查询
1.当角色已经分配到具体用户的结果集(已经实现)
hqlForcount = "from UserRoleRelaView t1 where t1.tbRoleId='"
+ tbroleId + "' and t1.relaId is not null";2.用户未有当前角色的结果集(请高手)3.用户未有或已经分配当前角色的结果集(请高手)请高手!!!
角色表 TbRole
用户角色关系表 User_Role_Rela
现已经建立此连:
create or replace view USER_ROLE_RELA_VIEW as
select t1.user_id,t1.user_name,t1.mobile,t2.tb_role_id,t2.rela_id from Tb_Users t1 left outer join User_Role_Rela t2 on t1.user_id=t2.user_id请问:
如何通过一条SQL查询
1.当角色已经分配到具体用户的结果集(已经实现)
hqlForcount = "from UserRoleRelaView t1 where t1.tbRoleId='"
+ tbroleId + "' and t1.relaId is not null";2.用户未有当前角色的结果集(请高手)3.用户未有或已经分配当前角色的结果集(请高手)请高手!!!
from UserRoleRelaView t1
where t1.user_id not in
(
select u1.user_id
from UserRoleRelaView u1
where u1.tb_role_id = '"+tbroleId +"'
)";3:
sql = "select *
from UserRoleRelaView t1
where t1.user_id not in
(
select u1.user_id
from UserRoleRelaView u1
where u1.tb_role_id = '"+tbroleId +"'
) union
select *
from UserRoleRelaView t1
where t1.tb_role_id='"+ tbroleId + "'
and t1.rela_id is not null";
hql = " select tu.user_id {TbUserVO.userId},user_name {TbUserVO.userName}, "
+ " mobile {TbUserVO.mobile},tb_role_id {TbUserVO.roleId} "
+ " from tb_users tu left outer join user_role_rela urr "
+ " on tu.user_id = urr.user_id and urr.tb_role_id = '"
+ tbroleId + "'";