SQL#三表联查和动态行转列

功能需求：Permission，Role,Role_Permission三个表的联合查询显示。

Role表：

Permission表

 

 Role_Permission表

 需求展示三表联合查询每个权限每个角色是否有权限：

根据需求需要进行三表联合查询，然后使用group by 和聚合函数进行分组，角色行转列

select ID,PermissionName,PLCAddress,
sum(生产操作员) as 生产操作员,
sum(生产技师) as 生产技师,sum(ME工程师) as ME工程师,sum(ME技术员) as ME技术员,
sum(供应商初级调试) as 供应商初级调试,sum(供应商高级调试) as 供应商高级调试
from(select a.ID,a.PermissionName,a.PLCAddress,c.RoleName, b.PermissionValue,
MAX(CASE RoleName WHEN '生产操作员' THEN PermissionValue ELSE 0 END) AS '生产操作员',
MAX(CASE RoleName WHEN '生产技师' THEN PermissionValue ELSE 0 END) AS '生产技师',
MAX(CASE RoleName WHEN 'ME工程师' THEN PermissionValue ELSE 0 END) AS 'ME工程师',
MAX(CASE RoleName WHEN 'ME技术员' THEN PermissionValue ELSE 0 END) AS 'ME技术员',
MAX(CASE RoleName WHEN '供应商初级调试' THEN PermissionValue ELSE 0 END) AS '供应商初级调试',
MAX(CASE RoleName WHEN '供应商高级调试' THEN PermissionValue ELSE 0 END) AS '供应商高级调试'
from GeeSun_Permission as a
left join GeeSun_Role_Permisson as b
on a.ID=b.PermissionId
left join GeeSun_Role as c
on b.RoleId=c.ID
group by a.ID,a.PermissionName,a.PLCAddress,c.RoleName, b.PermissionValue
) T
group by ID,PermissionName,PLCAddress,PermissionValue

但是上述SQL中角色的名称和数量是写死的，如果角色表的角色名称发生变化，或者新增、删除了角色，都需要进行SQL更改，不符合程序设计的开闭原则。进行优化后如下，实现了角色表的动态拼接功能。

DECLARE @cmdText    VARCHAR(8000);
DECLARE @tmpSql        VARCHAR(8000);
SET @cmdText = 'select ID,PermissionName,PLCAddress,' + CHAR(10);
SELECT @cmdText = @cmdText + ' sum(' +rolename+') as '+rolename +','+ CHAR(10)  FROM (SELECT DISTINCT top 100 ID,rolename FROM geesun_role order by ID ) T
SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2)
SET @cmdText = @cmdText + ' FROM(select a.ID,a.PermissionName,a.PLCAddress,c.RoleName, b.PermissionValue,';
SET @tmpSql =' ' + CHAR(10);
SELECT @tmpSql = @tmpSql + ' MAX(CASE RoleName WHEN '''+rolename+''' THEN PermissionValue ELSE 0 END) AS '''
+ rolename+''',' + CHAR(10) FROM  (SELECT DISTINCT top 100 ID,rolename FROM geesun_role order by ID  ) T
SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2)
SET @cmdText =@cmdText  +  @tmpSql+ 
' from GeeSun_Permission as a
left join GeeSun_Role_Permisson as b
on a.ID=b.PermissionId
left join GeeSun_Role as c
on b.RoleId=c.ID
group by a.ID,a.PermissionName,a.PLCAddress,c.RoleName, b.PermissionValue
) T
group by ID,PermissionName,PLCAddress,PermissionValue ';
EXECUTE (@cmdText);