比较 SQL 中的两个位掩码以查看是否有任何位匹配 [英] Comparing two bitmasks in SQL to see if any of the bits match

问题描述

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

Is there a way of comparing two bitmasks in Transact-SQL to see if any of the bits match? I've got a User table with a bitmask for all the roles the user belongs to, and I'd like to select all the users that have any of the roles in the supplied bitmask. So using the data below, a roles bitmask of 6 (designer+programmer) should select Dave, Charlie and Susan, but not 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

有什么想法吗?谢谢.

推荐答案

你的问题的答案是使用 Bitwise & 像这样:

The answer to your question is to use the Bitwise & like this:

SELECT * FROM UserTable WHERE Roles & 6 != 0

6 可以替换为您想要检查任何用户是否具有这些位中的一个或多个的位域的任意组合.在尝试验证这一点时，我通常发现将其以二进制形式手写出来会很有帮助.您的用户表如下所示:

The 6 can be exchanged for any combination of your bitfield where you want to check that any user has one or more of those bits. When trying to validate this I usually find it helpful to write this out longhand in binary. Your user table looks like this:

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

你的测试(6)是这样的

Your test (6) is this

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

如果我们检查每个人都在做 bitwaise 并且根据测试我们得到这些:

If we go through each person doing the bitwaise And against the test we get these:

        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) 

以上应证明结果不为零的任何记录都有一个或多个请求的标志.

The above should demonstrate that any records where the result is not zero has one or more of the requested flags.

如果你想检查这个，这是测试用例

Here's the test case should you want to check this

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

这篇关于比较 SQL 中的两个位掩码以查看是否有任何位匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！