100分求动态权限设计，已有示例数据

Question

--测试数据  
--创建用户表 
  create   table   Users(UserID   int,UseName   varchar(10))   
  insert   into   users
  select   1,'Admin'   
  union   all   select   2,'cwb'   
  union   all   select   3,'manage'   
--创建模块表
  create   table   Model(ModelID   int,ModelName   varchar(50))   
  insert   into   Model
  select   1,'货品资料'  union all
  select   2,'客户资料'  union all
  select   3,'报表'
--创建操作表
  create table OP(OpId  int ,OpName varchar(10))
  insert into OP
  select 1,'浏览' union all
  select 2,'新增' union all
  select 3,'删除' union all
  select 4,'导出' 
--创建 模块-操作 表
  create   table   ModelOP(MoldeOPId  int,ModelId int,OPid int)   
  insert   into   modelop
select   1,1,1   
  union   all   select   2,1,2   
  union   all   select   3,1,3   
  union   all   select   4,1,4   
  union   all   select   5,2,1 
  union   all   select   6,2,2   
  union   all   select   7,2,3   
  union   all   select   8,2,4   
  union   all   select   9,3,1   
  union   all   select   9,3,4
--创建 用户-模块-操作 表
  create table UserMOP(id int,userid int,modelid int,opid int)
  insert into usermop
select 1,1,1,1
union all select 2,1,1,2
  go   
    
 
--显示权限表的交叉语句
declare @SQL varchar(2000)
set @SQL='select model.modelid,modelname as 模块名,'
select @sql=@sql+'sum(CASE a.opname WHEN '''+opName+''' THEN 0 end) AS ''' +opName+
''',
' from (select opName from op)aselect @sql=left(@sql,Len(@sql)-3)+'  from 
model left outer join 
(select model.modelid, opname  from modelop join 
model on modelop.modelid=model.modelid join
op on modelop.opid=op.opid)aon model.modelID=a.modelid
 group by model.modelid, modelname order by model.modelid'
exec (@sql)
/*如何得到下面的结果
参数@userid=1
modelid  模块名 浏览    新增    删除    导出
1 货品资料 1 1 0 0
2 客户资料 0 0 0 0
3 报表 0 NULL NULL 0
如果参数@userid=2
modelid  模块名 浏览    新增    删除    导出
1 货品资料 0 0 0 0
2 客户资料 0 0 0 0
3 报表 0 NULL NULL 0*/
GO    
  --删除测试环境   
  drop   table   users,model,modelop,op,usermop
  
  

Answer 1

--测试数据  
--创建用户表 
  create   table   Users(UserID   int,UseName   varchar(10))   
  insert   into   users
  select   1,'Admin'   
  union   all   select   2,'cwb'   
  union   all   select   3,'manage'   
--创建模块表
  create   table   Model(ModelID   int,ModelName   varchar(50))   
  insert   into   Model
  select   1,'货品资料'  union all
  select   2,'客户资料'  union all
  select   3,'报表'
--创建操作表
  create table OP(OpId  int ,OpName varchar(10))
  insert into OP
  select 1,'浏览' union all
  select 2,'新增' union all
  select 3,'删除' union all
  select 4,'导出' 
--创建 模块-操作 表
  create   table   ModelOP(MoldeOPId  int,ModelId int,OPid int)   
  insert   into   modelop
select   1,1,1   
  union   all   select   2,1,2   
  union   all   select   3,1,3   
  union   all   select   4,1,4   
  union   all   select   5,2,1 
  union   all   select   6,2,2   
  union   all   select   7,2,3   
  union   all   select   8,2,4   
  union   all   select   9,3,1   
  union   all   select   9,3,4
--创建 用户-模块-操作 表
  create table UserMOP(id int,userid int,modelid int,opid int)
  insert into usermop
select 1,1,1,1
union all select 2,1,1,2
  go   
DECLARE @Userid INT
SET @Userid = 1
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT MO.ModelID, MO.ModelName AS 模块名'
SELECT @SQL = @SQL + '
,MAX(CASE WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NOT NULL AND UMO.userid IS NOT NULL THEN ''1'''
+'WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NOT NULL AND UMO.userid IS NULL THEN ''0'''
+'WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NULL AND UMO.userid IS NULL THEN ''NULL'' END) AS ''' + opName + ''''
FROM (SELECT opName FROM OP)ASET @SQL = @SQL + 'FROM
(SELECT * FROM Model, OP)MO
LEFT JOIN ModelOP MOP
ON MOP.ModelId = MO.ModelID AND MOP.OPid = MO.OPid
LEFT JOIN (SELECT * FROM UserMOP WHERE Userid = ' + CAST(@Userid AS VARCHAR) + ') UMO
ON UMO.OPid = MOP.OPid AND UMO.modelid = MOP.modelid
GROUP BY MO.ModelID, MO.ModelName
ORDER BY MO.ModelID'EXEC(@SQL)GO    
  --删除测试环境   
  drop   table   users,model,modelop,op,usermop
@userid = 1结果
ModelID     模块名                                                浏览   新增   删除   导出   
----------- -------------------------------------------------- ---- ---- ---- ---- 
1           货品资料                                               1    1    0    0
2           客户资料                                               0    0    0    0
3           报表                                                 0    NULL NULL 0
@userid = 2结果
ModelID     模块名                                                浏览   新增   删除   导出   
----------- -------------------------------------------------- ---- ---- ---- ---- 
1           货品资料                                               0    0    0    0
2           客户资料                                               0    0    0    0
3           报表                                                 0    NULL NULL 0

Answer 2

说明：
上面结果的NULL不是空值得NULL，而是varchar NULL
也就是，他是字符串，不是空值。

Answer 3

改为一下的代码，就是真正的数值0，1，和真正的空值。DECLARE @Userid INT
SET @Userid = 1
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT MO.ModelID, MO.ModelName AS 模块名'
SELECT @SQL = @SQL + '
,MAX(CASE WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NOT NULL AND UMO.userid IS NOT NULL THEN 1'
+'WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NOT NULL AND UMO.userid IS NULL THEN 0'
+'WHEN MO.opname = ''' + opname + ''' AND MOP.Opid IS NULL AND UMO.userid IS NULL THEN NULL END) AS ''' + opName + ''''
FROM (SELECT opName FROM OP)A