22,210
社区成员
发帖
与我相关
我的任务
分享
表一:User_Caozuoyuan 用户表
Id caozuoyuan_xm
1 张三
2 王二
表二:User_Role 角色表
Role_id Role_name
1 管理员
2 内勤
3 客服
表三:UserToRole 用户角色映射表
User_id Role_id
1 1
2 3
表四:ModuleName 菜单表
ModuleNo MKName
10001 人力资源
10002 客服系统
10003 会员管理
表五:Role_Module 角色到菜单映射表
RoleID ModuleID
1 10001
1 10002
1 10003
3 10003
表六 : User_Operation 按钮表
Id Name
102 新增
103 修改
104 删除
表七:User_MenuRoleOperation 菜单角色按钮映射表
MenuId OperationId RoleId
10003 102 1
10003 103 3
10003 104 3
10003 104 3
10002 102 3
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-10 08:13:26
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[User_Caozuoyuan]
if object_id('[User_Caozuoyuan]') is not null drop table [User_Caozuoyuan]
go
create table [User_Caozuoyuan]([Id] int,[caozuoyuan_xm] varchar(4))
insert [User_Caozuoyuan]
select 1,'张三' union all
select 2,'王二'
--> 测试数据:[User_Role]
if object_id('[User_Role]') is not null drop table [User_Role]
go
create table [User_Role]([Role_id] int,[Role_name] varchar(6))
insert [User_Role]
select 1,'管理员' union all
select 2,'内勤' union all
select 3,'客服'
--> 测试数据:[UserToRole]
if object_id('[UserToRole]') is not null drop table [UserToRole]
go
create table [UserToRole]([User_id] int,[Role_id] int)
insert [UserToRole]
select 1,1 union all
select 2,3
--> 测试数据:[ModuleName]
if object_id('[ModuleName]') is not null drop table [ModuleName]
go
create table [ModuleName]([ModuleNo] int,[MKName] varchar(8))
insert [ModuleName]
select 10001,'人力资源' union all
select 10002,'客服系统' union all
select 10003,'会员管理'
--> 测试数据:[Role_Module]
if object_id('[Role_Module]') is not null drop table [Role_Module]
go
create table [Role_Module]([RoleID] int,[ModuleID] int)
insert [Role_Module]
select 1,10001 union all
select 1,10002 union all
select 1,10003 union all
select 3,10003
--> 测试数据:[User_Operation]
if object_id('[User_Operation]') is not null drop table [User_Operation]
go
create table [User_Operation]([Id] int,[Name] varchar(4))
insert [User_Operation]
select 102,'新增' union all
select 103,'修改' union all
select 104,'删除'
--> 测试数据:[User_MenuRoleOperation]
if object_id('[User_MenuRoleOperation]') is not null drop table [User_MenuRoleOperation]
go
create table [User_MenuRoleOperation]([MenuId] int,[OperationId] int,[RoleId] int)
insert [User_MenuRoleOperation]
select 10003,102,1 union all
select 10003,103,3 union all
select 10003,104,3 union all
select 10003,104,3 union all
select 10002,102,3
--------------开始查询--------------------------
--select * from [User_MenuRoleOperation]
--select * from [User_Operation]
--select * from [Role_Module]
--select * from [ModuleName]
--select * from [UserToRole]
--select * from [User_Role]
SELECT a.id,a.caozuoyuan_xm,MKName,uo.name
FROM (
SELECT u.*,r.*
FROM [User_Caozuoyuan] u
INNER JOIN [UserToRole] ur ON ur.[USER_ID] = u.id
INNER JOIN [User_Role] r ON ur.role_id = r.role_id) a
INNER JOIN Role_Module rm ON a.role_id=rm.RoleID
INNER JOIN ModuleName m ON rm.[ModuleID]=m.ModuleNo
INNER JOIN User_MenuRoleOperation um ON a.role_id=um.RoleId
INNER JOIN User_Operation uo ON um.OperationId=uo.id
----------------结果----------------------------
/*
id caozuoyuan_xm MKName name
----------- ------------- -------- ----
1 张三 人力资源 新增
1 张三 客服系统 新增
1 张三 会员管理 新增
2 王二 会员管理 修改
2 王二 会员管理 删除
2 王二 会员管理 删除
2 王二 会员管理 新增
*/