我可以在 Sql Server 中对一组数字执行按位和吗?

2021-09-10 00:00:00 tsql sql-server

我有一个安全表，其中包含一个组和用户列表，每个组和用户都具有按位整数权限.对于每个给定的用户，我想对他们的所有组和他们的个人权限记录(如果存在)执行按位 AND.

I have a security table containing a list of groups and users with a bitwise integer permission for each. For each given user, I would like to perform a bitwise AND on all of their groups and of their personal permission record, if present.

当然，我可以在我的代码中轻松地做到这一点，但我更愿意在数据库中做到这一点，因为可能有数以千计的项目我正在查询其权利.

Of course, I can easily do this in my code, but I'd rather do it in the database as there could be thousands of items I am querying the rights for.

相比游标，我更喜欢基于集合的解决方案.

I would prefer a set-based solution over a cursor.

请注意，我无法控制架构.

Note than I do not have control over the schema.

推荐答案

如果您想要按位或单位值的所有值，基于集合的解决方案是可能的:按位求和

A set-based solution is possible if all the values that you want to bitwise or are single-bit values: Performing a bitwise sum

或者，您可以使用不太优雅的方法对非唯一值集进行基于模糊集的按位运算:

Alternatively, you can use a less-elegant method for vaguely-set-based bitwise operations on sets of non-unique values:

DECLARE @BitSum INT
SET @BitSum = 0
SELECT @BitSum = @BitSum | BitValue
FROM (
    SELECT 1 AS BitValue
    UNION SELECT 7
    UNION SELECT 16
) AS SampleValues
SELECT @BitSum

Hugo Kornelis 在另一篇文章中非常全面地回答了这个问题:http://www.eggheadcafe.com/software/aspnet/33139293/b​​itwise-aggregate-function.aspx

Hugo Kornelis answers the question pretty comprehensively in this other post: http://www.eggheadcafe.com/software/aspnet/33139293/bitwise-aggregate-function.aspx

相关文章