主页 / dba / 问题 / 102994
Accepted
JoeNahmias
Asked: 2015-06-02 14:29:11 +0800 CST

在 t-sql 中解码和聚合 BitMask

  • 772

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

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 list-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

有任何想法吗？

sql-server sql-server-2012

1 个回答

  1. Best Answer

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

    1. 拥有一个权限值0since0意味着“没有权限”是没有意义的。
    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 创建可以执行此类String.Join()操作的用户定义聚合 (UDA)。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 UDA 一定是更好的选择，我只是指出这是一种选择，并且根据具体要求，可能会更好。

    或者，从 SQL Server 2017 开始，有一个内置的聚合函数STRING_AGG可以处理这个问题。

    针对 Bitmask 测试 Bit 值的一种稍微不同的方法是将它们的按位与运算与 Bit 值本身进行比较，而不是对照<> 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
    • 4

相关问题