在SQL中比较两个二进制掩码以查看是否存在任何匹配的位

Question

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

用户表
----------
ID  用户名     角色
1   Dave      6
2   Charlie   2
3   Susan     4
4   Nick      1
角色表
-----------
ID  角色
1   管理员
2   程序员
4   设计师

有什么想法吗？谢谢。

- Nick Crowther

Answer 1

回答你的问题是使用位运算符 & ，像这样：

SELECT * FROM UserTable WHERE Roles & 6 != 0

数字6可以与您的位域(bitfield)中的任何组合交换，其中您想要检查任何用户是否具有这些位(bit)中的一个或多个。在尝试验证此内容时，通常需要以二进制形式手写出来以便更好地理解。您的用户表如下：

        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

如果我们对每个人进行按位与测试，则会得到以下结果：

        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

- Jamiec

---

理想的解决方案，谢谢。已经太久远，无法记得当时如何解决问题，但这几乎肯定是一个更好的解决方案！ - Nick

这是一篇不错的阅读材料 - https://www.sqlservercentral.com/articles/introduction-to-bitmasking-in-sql-server-part-2-1 - Premchandra Singh

Answer 2

使用Transact-SQL 按位与运算符 "&" 并将结果与零进行比较。更好的方法是，不要将角色编码为整数列的位，而是使用布尔列，每个角色一个列。然后，您的查询将简单易懂，便于设计师和程序员使用。如果您预计应用程序的生命周期内角色会经常更改，则使用多对多表来映射用户和其角色之间的关联。这两种替代方案比依赖按位AND运算符的存在更具可移植性。

- Diomidis Spinellis

---

& 操作符可用于将位掩码与单个角色进行比较，但不能将位掩码与另一个位掩码进行比较。我认为我需要使用动态 SQL，并将每个角色放入 where 子句中。 - Nick

@Nick - 错了 - 看看我的答案。 - Jamiec

Answer 3

SELECT * FROM UserTable WHERE Roles & 6 > 0

- Ben

Answer 4

从表中选择所有满足mask1和mask2条件的数据。

- vsevik

Answer 5

例子：

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

- ScottE

---

这会得出错误的结果。尝试使用4 - Susan和Dave都有4 - 但它只返回Susan。 - Jamiec

Answer 6

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

SELECT * FROM UserTable WHERE Roles & 2 = 2

- Sklivvz

---

OP正在寻找程序员或设计师。 - ScottE