---------------------------------------------------------------- -- 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 王二 会员管理 新增*/
-- 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 王二 会员管理 新增*/