Comparing two bitmasks in SQL to see if any of the

问题:

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

Any ideas? Thanks.

回答1:

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

SELECT * FROM UserTable WHERE Roles & 6 != 0

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

Your test (6) is this

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

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.

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

回答2:

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.

回答3:

SELECT * FROM UserTable WHERE Roles & 6 > 0

回答4:

SELECT * FROM table WHERE mask1 & mask2 > 0

回答5:

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

回答6:

To find all programmers use:

SELECT * FROM UserTable WHERE Roles & 2 = 2