在 t-sql 中解碼和聚合 BitMask

我有一個包含儲存權限的位遮罩欄位的表，其中每個位表示是否授予特定權限。這是一個簡化的範例：

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

有任何想法嗎？

首先，我們需要對原程式碼做一些小調整：

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

引用自：https://dba.stackexchange.com/questions/102994