主页 / dba / 问题 / 246566
Accepted
Amira Bedhiafi
Asked: 2019-08-30 07:23:44 +0800 CST

获取每个员工的作用域角色

  • 772

我有以下查询，对于每个员工，我都会得到他不同角色的列表：

SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId
FROM Employee e
LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId
LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId
WHERE e.EmployeeId=54

这是先前查询输出的摘录： 在此处输入图像描述

我想获得任何员工的每个标签（角色）的范围（这里是 EmployeeId=54）：

ValueId背后的逻辑：

在此处输入图像描述

  • NULL 表示我们不关心范围，因为您的角色是
    全局的（不在特定实体的范围内）
  • dbo.Employee_Department 表中的 2 匹配“DNA”产品。
  • dbo.Employee_Department 表中的 1 匹配“软件工厂”部门。

这意味着：角色可以链接到数据库中的任何实体（参见 [SchemaName]、[TableName]、[ColumnName] 中的 [Function].[Role] 列）。在 Employee_Role 中，ValueId 与指定表的 PK 匹配在[功能].[角色]中。

因此，要为 EmployeeId = 5255 获取角色 RoleId = 250 的标签，获取 ValueId=2 并查看具有 Schema HoA 的表 Product 并获取具有 PrimaryKey 的记录（可以从一个表更改为另一个）=ValueId = 2、对应的标签栏与角色标签匹配如下： 在此处输入图像描述

更新

SELECT e.EmployeeId,r.EntityRoleId, r.Label,er.ValueId 
FROM Employee e
LEFT JOIN Employee_Role er ON e.EmployeeId = er.EmployeeId
LEFT JOIN [Function].[Role] r ON er.RoleId = r.EntityRoleId
INNER JOIN sys.indexes i on object_name(i.object_id) = r.TableName 
INNER JOIN sys.tables t on i.object_id = t.object_id and SCHEMA_NAME(t.schema_id) = r.SchemaName
INNER JOIN sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id 
WHERE i.is_primary_key = 1 
ORDER BY e.EmployeeId

我可以得到对应于主键（ValueId）=2的列，我想得到对应表中存在的列Label的内容，即截图中的“DNA”。

我的问题是如何在这种情况下获得每个角色的标签？

sql-server t-sql

1 个回答

  1. Best Answer

    这可以通过动态 SQL 来解决。或者，您可以将结果行转换为 XML，其中每一列都将转换为一个属性。这允许您从行中生成名称/对值，并根据您感兴趣的列名称进行过滤。

    这是一个 POC，其中 CTE 测试的内容将是您的行，@Field 是您尝试提取的字段。我添加了一个 RowNumber 字段以防返回多行。注意：我希望动态 SQL 性能更高。

    DECLARE @Field NVARCHAR(128) = 'Label';

WITH Test AS (
    SELECT 1 a, 2 b, 'greg' Label
    UNION SELECT 2 a, 4 b, 'jan' Label
)



SELECT 
    col.value('.././@RowNumber', 'int') AS RowNumber -- Not needed for single row results
    , col.value('local-name(.)', 'nvarchar(max)') AS [name]
    , col.value('.', 'nvarchar(max)') AS [value] 
FROM
(
    SELECT CAST(
        (
            SELECT 
                *
                , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber -- Not needed for single row results
            FROM 
                Test 
            FOR XML AUTO, ROOT
        ) -- Wrap to treat as a single result
    AS XML) -- convert to XML to wok with it
) D(X)
    CROSS APPLY X.nodes('//@*') N(col) -- Get all Attributes
WHERE col.value('local-name(.)', 'nvarchar(max)') = @field -- limit to the field we care about
    • 2

相关问题