代码之家 › 专栏 › 技术社区 › Arno Voerman

按第二个表中的所有记录更新表

sql-update sql-server sql

Arno Voerman · 技术社区 · 5 年前

早晨,

我有两张桌子。第一个表(security rules)是一个安全规则列表:

ID  srRight srRole
1   4       NULL    
2   2       32

第二个表(项目)是项目列表:

ProjId  prRight prRole
1       0       NULL
2       0       32
3       0       NULL

prRight 基于两个表中的角色的列。这个 Right 值是按位组织的。我使用了以下SQL更新查询来执行此操作:

Update  Projects
        -- Perform binary sum
Set     prRight = prRight | srRight
From    SecurityRules
Where   (srRole is Null)                            --Always apply srRight if srRole is not defined
        OR (srRole is Not Null And srRole=prRole)   --Apply right if both roles are equal

预期结果是:

ProjId  prRight prRole
1   4   NULL
2   6   32
3   4   NULL

ProjId  prRight prRole
1   4   NULL
2   4   32
3   4   NULL

看起来更新只由SecurityRules表的第一条记录完成。我需要将SecurityRules表中的所有记录应用于Project表中的所有记录。如果我创建一个简单的循环并手动循环security rules中的所有记录,那么它可以正常工作,但是如果必须将10个安全规则与2000个项目进行比较,则性能非常差。。。

阿诺

1 回复 | 直到 5 年前

Nick SamSmith1986 5 年前

这个答案是基于 this answer 用于生成按位或的值。它使用CTE为每个权限值生成一个位掩码,然后按位或通过求和每个权限值中存在的不同位掩码来生成整个位掩码。最后一个CTE的输出用于更新 Projects 表格:

WITH Bits AS (
  SELECT 1 AS BitMask
  UNION ALL
  SELECT 2 * BitMask FROM Bits
  WHERE BitMask < 65536
),
NewRights AS (
  SELECT ProjId, SUM(DISTINCT BitMask) AS NewRight
  FROM Projects p
  JOIN SecurityRules s ON s.srRole IS NULL OR s.srRole = p.prRole
  JOIN Bits b ON b.BitMask & s.srRight > 0
  GROUP BY ProjID
)
UPDATE p
SET p.prRight = n.NewRight
FROM Projects p
JOIN NewRights n ON n.ProjId = p.ProjId

结果 项目 表格:

ProjId  prRight     prRole
1       4           null
2       6           32
3       4           null

Demo on dbfiddle

Gordon Linoff 5 年前

如果我没听错的话,你在 srRole 列,然后是适用于所有人的默认规则。

最简单的方法(在本例中)是 join update :

update p
    Set prRight = p.prRight | srn.srRight | coalesce(sr.srRight, 0)
From Projects p join
     SecurityRules srn
     on srRole is null left join
     SecurityRules sr
     on sr.srRole = p.prRole;

Here

假设没有默认规则,您可能会更安全。还有那个 prRight NULL :

update p
    Set prRight = coalesce(p.prRight, 0) | coalesce(srn.srRight, 0) | coalesce(sr.srRight, 0)
From Projects p left join
     SecurityRules srn
     on srRole is null left join
     SecurityRules sr
     on sr.srRole = p.prRole;

也就是说,我建议你考虑修改你的数据模型。在编程语言中,位摆弄很有趣。然而,它通常不是数据库中的最佳方法。相反,使用连接表,除非应用程序真正需要位开关。