在T-SQL中比较位数组和位掩码

Question

我已经使用T-SQL工作了相当长的时间，但我从未在WHERE子句中见过二进制“与”或“或”操作符。现在，我正在开发一个新应用程序，该应用程序从应用位掩码将受益。 假设我有16个产品等级。每个等级都由位[]列中的一位表示。因此，A109等级将是0000000000000001，B704等级将是0000001000000000，V64等级为0100000000000000，依此类推。任何等级在其数组列中只能有单个1。现在假设我可以在制造过程中将这3个等级中的每一个转化为另一个。因此，我的位掩码为0100001000000001。如何编写WHERE子句以列出所有这些3个等级的项？

- ArtK

---

请看这里：http://technet.microsoft.com/zh-cn/library/ms176122.aspx - cha

你究竟想要实现什么？你能举个例子说明一下你所拥有的数据，以及匹配和不匹配的一两行吗？也就是说，如果你传入一个包含3个项目的位掩码值，那么匹配项必须至少具有这3个项目中的a）至少这3个项目，b）只有这3个项目，还是c）这3个项目中的任意一个？很可能这种方法（因此被接受的答案）完全是错误的。 - Solomon Rutzky

Answer 1

我做了更多的研究，最好的解决方案是使用按位与运算符来比较掩码，像这样：

WHERE mask1 & mask2 <> 0

这很简单、易懂且连贯。

- ArtK

Answer 2

对我来说也是第一次。有趣。

declare @A109 int = 1;
declare @B704 int = 512;
declare @V64  int = 16384;
declare @Xx   int = 32;

declare @mask int = 16897; --@A109+@B704+@V64

create table #MyData (
    name char(2),
    value int);

insert #MyData
values ('a', @A109), ('b1', @B704), ('b2',@B704), ('c', @Xx);

select
    name,
    value
from #MyData
where (value & @mask) in (@A109, @B704, @V64);

drop table #MyData;

看起来你不能在二进制数据上执行位运算！"在位运算中，只有一个表达式可以是二进制或varbinary数据类型"

- Michael Green

---

这非常不错，但由于我需要在应用程序中使用此搜索功能，所以我无法提供解决方案列表，我需要提供一个掩码形式的参数。 - ArtK

Answer 3

在您的用户界面中，允许用户选择任意数量的成绩。在幕后，每个成绩都映射到一个整数 - 来自MarkD解决方案的Grading表。在应用程序中对这些整数求和。将总和传递给SP。

SQL：

create procedure dbo.GetMaskedList @mask int = 0;
...
    WHERE BinGrading = @mask;

UI:

int mask = 0;
foreach(item in selectList)
{
    mask += item.BinScore;
}

exec GetMaskedList @mask = mask;

- Michael Green

Answer 4

在这种情况下，WHERE子句会非常简单，因为您将处理二进制数的十进制（基数10）对应项... 可爱的地方在于JOIN。 为了澄清，该方法不使用bit[]列或BINARY类型 - 只使用INT。

希望您会发现这很有帮助。

DECLARE @G1 INT = 1,    --  0000001
        @G2 INT = 2,    --  0000010
        @G3 INT = 4,    --  0000100
        @G4 INT = 8,    --  0001000
        @G5 INT = 16,   --  0010000
        @G6 INT = 32,   --  0100000
        @G7 INT = 64    --  1000000

;WITH Grading (Grade, BinScore) AS
(
    SELECT  'G1',   1   UNION ALL
    SELECT  'G2',   2   UNION ALL
    SELECT  'G3',   4   UNION ALL
    SELECT  'G4',   8   UNION ALL
    SELECT  'G5',   16  UNION ALL
    SELECT  'G6',   32  UNION ALL
    SELECT  'G7',   64

) 
,Product (ProductName, BinGrading) AS
(
    SELECT 'Foobar',        73  UNION ALL
    SELECT 'Franglesnap',   3   UNION ALL
    SELECT 'Mebble',        32
)
SELECT *
FROM Product
WHERE BinGrading = (@G1 + @G4 + @G7)
--  Alternatively...
--SELECT *
--FROM Product  P
--JOIN Grading  G   ON P.Bingrading & G.BinScore > 0

- MarkD

---

我认为你已经接近答案了，但我可能需要更多的指导。我想在一个存储过程中执行过滤，只传递一个参数。该参数可以是整数或等效的位数组。我真的需要使用 UNION 或 JOIN 来将位掩码“解析”为单个位吗？ - ArtK

我已经发布了另一个答案以获得更多的编辑空间，但是@MarkD拥有它。 - Michael Green