角色表UserRole(UserID,RoleID) 和 用户部门表UserDept(UserID,DeptID) 连接查询 然后GROUP BU DeptID , RoleID
select a.DeptName,c.UserName,d.RoleID from Depart a inner join userDept b on a.id=b.deptid inner join User c on b.UserId=c.id inner join UserRole d on c.id=d.userid where exists(select 1 from userRole where UserId=d.UserID and RoleID<>d.RoleID)
/* 有用户角色表userrole(userid,roleid), 用户表user(userid,username), 用户部门表userdept(userid,deptid), 部门表dept(deptid,deptname). 现在要统计出在所有部门中一个角色出现多人的情况。 比如统计哪些部门有超过2个人的"系统管理员"角色, 分别是哪些人? */ go if object_id('userrole') is not null drop table userrole if object_id('[user]') is not null drop table [user] if object_id('userdept') is not null drop table userdept if object_id('dept') is not null drop table dept go create table userrole( userid char(10), roleid char(10) ) insert into userrole values('1001','role01') insert into userrole values('1002','role03') insert into userrole values('1003','role03') insert into userrole values('1004','role04') insert into userrole values('1005','role02') insert into userrole values('1006','role01') insert into userrole values('1007','role01') insert into userrole values('1008','role03') create table [user]( userid char(10), userbame char(10) ) insert into userrole values('1001','tracy') insert into userrole values('1002','kobe') insert into userrole values('1003','lucy') insert into userrole values('1004','lily') insert into userrole values('1005','tom') insert into userrole values('1006','nash') insert into userrole values('1007','rose') insert into userrole values('1008','kaka') create table userdept( userid char(10), deptid char(10) ) go insert into userdept values('1001','01') insert into userdept values('1002','03') insert into userdept values('1003','03') insert into userdept values('1004','02') insert into userdept values('1005','01') insert into userdept values('1006','01') insert into userdept values('1007','01') insert into userdept values('1008','03') go create table dept( deptid char(10), deptname char(10) ) insert into dept values('01','sales') insert into dept values('02','et') insert into dept values('03','product') --现在要统计出在所有部门中一个角色出现多人的情况 select b.deptname,b.roleid from( select u.userid,u.roleid,d.deptname from userrole u inner join userdept a on a.userid=u.userid inner join dept d on d.deptid=a.deptid)b group by b.deptname,b.roleid having COUNT(1)>=2 ------------------------------------------------------ /* deptname roleid sales role01 product role03 */ ------------------------------------------------------
from ...
group by ...
having count(1)>2
老大 你表设计的对麽 我看着别扭
用户部门表UserDept(UserID,DeptID) 中的UserID是角色表的UID还是User表的UID如果把角色表的UserID改成 RoleName ; User 加个RoleID 可能更好吧小弟菜鸟 这么想的..
和
用户部门表UserDept(UserID,DeptID)
连接查询
然后GROUP BU DeptID , RoleID
from Depart a inner join userDept b on a.id=b.deptid
inner join User c on b.UserId=c.id
inner join UserRole d on c.id=d.userid
where exists(select 1 from userRole where UserId=d.UserID and RoleID<>d.RoleID)
有用户角色表userrole(userid,roleid),
用户表user(userid,username),
用户部门表userdept(userid,deptid),
部门表dept(deptid,deptname).
现在要统计出在所有部门中一个角色出现多人的情况。
比如统计哪些部门有超过2个人的"系统管理员"角色,
分别是哪些人?
*/
go
if object_id('userrole') is not null
drop table userrole
if object_id('[user]') is not null
drop table [user]
if object_id('userdept') is not null
drop table userdept
if object_id('dept') is not null
drop table dept
go
create table userrole(
userid char(10),
roleid char(10)
)
insert into userrole values('1001','role01')
insert into userrole values('1002','role03')
insert into userrole values('1003','role03')
insert into userrole values('1004','role04')
insert into userrole values('1005','role02')
insert into userrole values('1006','role01')
insert into userrole values('1007','role01')
insert into userrole values('1008','role03')
create table [user](
userid char(10),
userbame char(10)
)
insert into userrole values('1001','tracy')
insert into userrole values('1002','kobe')
insert into userrole values('1003','lucy')
insert into userrole values('1004','lily')
insert into userrole values('1005','tom')
insert into userrole values('1006','nash')
insert into userrole values('1007','rose')
insert into userrole values('1008','kaka')
create table userdept(
userid char(10),
deptid char(10)
)
go
insert into userdept values('1001','01')
insert into userdept values('1002','03')
insert into userdept values('1003','03')
insert into userdept values('1004','02')
insert into userdept values('1005','01')
insert into userdept values('1006','01')
insert into userdept values('1007','01')
insert into userdept values('1008','03')
go
create table dept(
deptid char(10),
deptname char(10)
)
insert into dept values('01','sales')
insert into dept values('02','et')
insert into dept values('03','product')
--现在要统计出在所有部门中一个角色出现多人的情况
select b.deptname,b.roleid from(
select u.userid,u.roleid,d.deptname from userrole u
inner join userdept a on a.userid=u.userid
inner join dept d on d.deptid=a.deptid)b
group by b.deptname,b.roleid
having COUNT(1)>=2
------------------------------------------------------
/*
deptname roleid
sales role01
product role03
*/
------------------------------------------------------