Comparaison de deux masques de bits en SQL pour voir si l'un des bits correspond

Question

Existe-t-il un moyen de comparer deux masques de bits en Transact-SQL pour voir si l'un des bits correspond ? J'ai une table Utilisateur avec un masque de bits pour tous les rôles auxquels l'utilisateur appartient, et j'aimerais sélectionner tous les utilisateurs qui ont un masque de bits. tous des rôles dans le masque de bits fourni. Ainsi, en utilisant les données ci-dessous, un masque de rôles de 6 (concepteur+programmeur) devrait sélectionner Dave, Charlie et Susan, mais pas 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

Des idées ? Merci de votre compréhension.

Answer 1

La réponse à votre question est l'utilisation de la fonction Bitwise & comme ceci :

SELECT * FROM UserTable WHERE Roles & 6 != 0

En 6 peut être échangé contre n'importe quelle combinaison de votre champ de bits lorsque vous voulez vérifier que n'importe quel utilisateur possède un ou plusieurs de ces bits. Lorsque j'essaie de valider cela, je trouve généralement utile de l'écrire à la main en binaire. Votre table d'utilisateurs ressemble à ceci :

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

Votre test (6) est le suivant

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

Si l'on passe en revue chaque personne effectuant le test bitwaise et qu'on le compare au test, on obtient les résultats suivants :

        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) 

Ce qui précède devrait démontrer que tous les enregistrements dont le résultat est différent de zéro possèdent un ou plusieurs des indicateurs demandés.

Edit : Voici le cas de test si vous voulez le vérifier

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

o

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

o

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

Answer 2

Utiliser le logiciel Transact-SQL opérateur ET binaire "&" et comparez le résultat à zéro. Mieux encore, au lieu de coder les rôles comme des bits dans une colonne d'entiers, utilisez des colonnes booléennes, une pour chaque rôle. Votre requête serait alors simplement conviviale pour le concepteur ET le programmeur. Si vous vous attendez à ce que les rôles changent beaucoup au cours de la durée de vie de votre application, utilisez alors une table many-to-many pour mapper l'association entre les utilisateurs et leurs rôles. Les deux alternatives sont plus portables que l'existence de l'opérateur bitwise-AND.

Answer 3

SELECT * FROM UserTable WHERE Roles & 6 > 0

Answer 4

SELECT * FROM table WHERE mask1 & mask2 > 0

Answer 5

Exemple :

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