请问如何通过一下7张表 通过用户ID 联合查询出 该用户有那些菜单权限及按钮权限，注我的是SQL2000
表一：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]