在t中解码和聚合BitMask

Question

我有一个表，其中包含存储权限的位掩码字段，其中每个位表示是否授予特定权限。下面是一个简化的示例：

DECLARE @T TABLE (id smallint identity, BitMask tinyint);
INSERT INTO @T (BitMask) VALUES
  (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);


SELECT
  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (t.id, 0, 'Can X'),
    (t.id, 1, 'Can Y'),
    (t.id, 2, 'Can Z')
  ) bm(id, BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum) <> 0
) bm

这将返回以下信息：

id     BitMask BitNum      Permission
------ ------- ----------- ----------
1      0       NULL        NULL
2      1       0           Can X
3      2       1           Can Y
4      3       0           Can X
4      3       1           Can Y
5      4       2           Can Z
6      5       0           Can X
6      5       2           Can Z
7      6       1           Can Y
7      6       2           Can Z
8      7       0           Can X
8      7       1           Can Y
8      7       2           Can Z
9      8       NULL        NULL
10     9       0           Can X

(15 row(s) affected)

到目前一切尚好。当我尝试按id进行聚合时，问题就来了，这样我就拥有了一个字段中的所有权限。我尝试添加以下APPLY子句来执行标准的XML列表-string-agg，但是我得到了一个错误Invalid object name 'bm'.：

OUTER APPLY (
  SELECT 
   ParamList = STUFF(
     (
       SELECT  '; ' + a.Permission
       FROM bm a WHERE a.id = b.id
       ORDER BY a.BitNum
       FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
     ), 1, 2, ''
   )
  FROM bm b
  GROUP BY b.id
) q

有什么想法吗？

Answer 1

首先，我们需要对原始代码做一些小的调整：

1. 拥有0的许可权值是没有意义的，因为0的意思是“无权限”。
2. "BitNum“不是您在POWER函数中使用的直接值。如果您需要1的“位”值，这来自于将2提高到0的能力。因此，您需要从“1”中减去BitNum，以便在POWER函数中使用。

考虑到这两个更改，对原始查询的以下更改将为您提供正确的初始结果集：

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
  ) bm(BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum - 1) <> 0
) bm

该查询可以进一步简化/简化为简单的LEFT JOIN，如下所示：

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0

结果(12行)：

id  BitMask BitNum  Permission
1   0       NULL    NULL
2   1       1       Can Y
3   2       2       Can Z
4   3       1       Can Y
4   3       2       Can Z
5   4       NULL    NULL
6   5       1       Can Y
7   6       2       Can Z
8   7       1       Can Y
8   7       2       Can Z
9   8       NULL    NULL
10  9       1       Can Y

接下来，既然我们有了正确的基本查询，您就不能简单地添加一个APPLY，因为您的原始查询拥有作为单独行的每个权限，但是现在您希望将它们按"BitMask“分组到一个行中。因此，您需要重组请求如下(或类似的内容)：

SELECT   t.id, t.BitMask, PermissionList = 
(
  SELECT PermissionList = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) <> 0
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

结果(10行)：

id  BitMask     PermissionList
1   0           NULL
2   1           Can Y
3   2           Can Z
4   3           Can Y; Can Z
5   4           NULL
6   5           Can Y
7   6           Can Z
8   7           Can Y; Can Z
9   8           NULL
10  9           Can Y

您还可以选择使用SQLCLR创建一个用户定义聚合(UDA)，该聚合可以执行这种类型的String.Join()操作。这样的聚合函数已经存在于SQL#库中(我是该库的作者，但这个函数在免费版本中可用)，尽管使用逗号(没有空格)作为分隔符是硬编码的，如果没有匹配，则返回空字符串而不是NULL。但是，它确实提供了一个更具可读性的查询：

SELECT  t.id, t.BitMask, SQL#.Agg_Join(bm.Permission) AS [PermissionList]
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0
GROUP BY t.id, t.BitMask;

这并不是说使用SQLCLR必然是更好的选择，我只是指出这是一个选择，取决于具体的需求，可能会更好。

或者，从Server 2017开始，有一个内置聚合函数字符串_阿格可以处理这个问题。

相对于位掩码测试比特值的一种稍微不同的方法是将它们的比特顺序和操作与位值本身进行比较，而不是与<> 0进行比较：

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

 -- based on "improved" query
SELECT   t.id, t.BitMask, [PermissionList] = 
(
  SELECT [PermissionList] = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (0, 'Default'),
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) = POWER(2, bm.BitNum - 1)
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

这使您更接近于将0作为一个值使用，但是您会遇到在所有记录中隐式地使用该值的问题。上面的结果(请注意，ON条件已经更改，我将0记录添加回)：

id  BitMask     PermissionList
1   0           Default
2   1           Default; Can Y
3   2           Default; Can Z
4   3           Default; Can Y; Can Z
5   4           Default
6   5           Default; Can Y
7   6           Default; Can Z
8   7           Default; Can Y; Can Z
9   8           Default
10  9           Default; Can Y