比较 SQL 中的两个位掩码以查看是否有任何位匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/143712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing two bitmasks in SQL to see if any of the bits match
提问by 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 anyof 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.
有没有办法比较 Transact-SQL 中的两个位掩码以查看是否有任何位匹配？我有一个 User 表，其中包含用户所属的所有角色的位掩码，我想选择在所提供的位掩码中具有任何角色的所有用户。因此，使用下面的数据，角色位掩码为 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
Any ideas? Thanks.
有任何想法吗？谢谢。
回答by Jamiec
The answer to your question is to use the Bitwise &like this:
您的问题的答案是&像这样使用 Bitwise ：
SELECT * FROM UserTable WHERE Roles & 6 != 0
The 6can 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:
将6能为您要检查任何用户有一个或一个以上的比特位域的任意组合进行交换。在尝试验证这一点时，我通常发现将其以二进制形式手写出来会很有帮助。您的用户表如下所示：
1 2 4
------------------
Dave 0 1 1
Charlie 0 1 0
Susan 0 0 1
Nick 1 0 0
Your test (6) is this
你的测试（6）是这样的
1 2 4
------------------
Test 0 1 1
If we go through each person doing the bitwaise And against the test we get these:
如果我们通过每个人做 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)
The above should demonstrate that any records where the result is not zero has one or more of the requested flags.
以上应该证明结果不为零的任何记录都有一个或多个请求的标志。
Edit: 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
or
或者
select * from test where (roles & 2) != 0 // returns Dave & Charlie
or
或者
select * from test where (roles & 7) != 0 // returns dave, charlie, susan & nick
回答by Diomidis Spinellis
Use the Transact-SQL bitwise AND operator"&" and compare the result to zero. Even better, instead of coding the roles as bits of an integer column, use boolean columns, one for each role. Then your query would simply be designer AND programmer friendly. If you expect the roles to change a lot over the lifetime of your application, then use a many-to-many table to map the association between users and their roles. both alternatives are more portable than relying on the existence of the bitwise-AND operator.
使用 Transact-SQL按位 AND 运算符“&”并将结果与零进行比较。更好的是，不要将角色编码为整数列的位，而是使用布尔列，每个角色一个。那么您的查询将只是设计师和程序员友好的。如果您希望角色在应用程序的整个生命周期内发生很大变化，那么请使用多对多表来映射用户与其角色之间的关联。与依赖按位与运算符的存在相比，这两种选择都更具可移植性。
回答by Ben
SELECT * FROM UserTable WHERE Roles & 6 > 0
回答by vsevik
SELECT * FROM table WHERE mask1 & mask2 > 0
SELECT * FROM table WHERE mask1 & mask2 > 0
回答by ScottE
example:
例子：
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
回答by Sklivvz
To find all programmers use:
要查找所有程序员，请使用：
SELECT * FROM UserTable WHERE Roles & 2 = 2
