比较 SQL 中的两个位掩码以查看是否有任何位匹配

Question

有没有办法比较 Transact-SQL 中的两个位掩码以查看是否有任何位匹配？ 我有一个用户表，其中包含用户所属的所有角色的位掩码，并且我想选择在提供的位掩码中具有任何角色的所有用户。 因此，使用下面的数据，角色位掩码为 6（设计师+程序员）应选择 Dave、Charlie 和 Susan，但不选择 Nick。

User Table
----------
ID  Username  Roles
1   Dave      6
2   Charlie   2
3   Susan     4
4   Nick      1

Roles Table
-----------
ID  Role
1   Admin
2   Programmer
4   Designer

有任何想法吗？ 谢谢。

Answer 1

使用 Transact-SQL 按位 AND 运算符“&” 并将结果与​​零进行比较。 更好的是，不要将角色编码为整数列的位，而是使用布尔列，每个角色一个。 那么您的查询将是对设计师和程序员友好的。 如果您预计角色在应用程序的生命周期内发生很大变化，则可以使用多对多表来映射用户及其角色之间的关联。 这两种替代方案都比依赖于按位与运算符的存在更具可移植性。

Answer 2

SELECT * FROM UserTable WHERE Roles & 6 > 0

Answer 3

SELECT * FROM 表 WHERE mask1 & 掩模2> 0

Answer 4

例子：

DECLARE @Mask int
SET @Mask = 6

DECLARE @Users TABLE
(
ID int,
Username varchar(50),
Roles int
)

INSERT INTO @Users (ID, Username, Roles) 
SELECT 1, 'Dave', 6
UNION
SELECT 2, 'Charlie', 2
UNION
SELECT 3, 'Susan', 4
UNION
SELECT 4, 'Nick', 1

SELECT * FROM @Users WHERE Roles & @Mask > 0

Answer 5

要查找所有程序员，请使用：

SELECT * FROM UserTable WHERE Roles & 2 = 2

Answer 6

您问题的答案是使用按位 &，如下所示：

SELECT * FROM UserTable WHERE Roles & 6 != 0

6 可以交换为您想要检查任何用户是否拥有位字段的任意组合或更多这些位。 当尝试验证这一点时，我通常发现以二进​​制形式直接写出来会很有帮助。 您的用户表如下所示：

        1   2   4
------------------
Dave    0   1   1
Charlie 0   1   0
Susan   0   0   1   
Nick    1   0   0

您的测试 (6) 是这样的

        1   2   4
------------------
Test    0   1   1

如果我们检查每个人执行 bitwaise 的情况，我们会得到以下结果：

        1   2   4
------------------
Dave    0   1   1   
Test    0   1   1
Result  0   1   1 (6)

Charlie 0   1   0
Test    0   1   1
Result  0   1   0 (2)

Susan   0   0   1
Test    0   1   1
Result  0   0   1 (4)

Nick    1   0   0
Test    0   1   1
Result  0   0   0 (0) 

上面应该表明，结果不为零的任何记录都具有一个或多个请求的标志。

这是测试用例

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',6
    UNION SELECT 2,'Charlie',2
    UNION SELECT 3,'Susan',4
    UNION SELECT 4,'Nick',1
)
select * from test where (roles & 6) != 0  // returns dave, charlie & susan

编辑：如果您想检查这个或

select * from test where (roles & 2) != 0 // returns Dave & Charlie

或，

select * from test where (roles & 7) != 0 // returns dave, charlie, susan & nick