[SQL]解析Bitwise欄位加總後，如何呈現選項清單

2014-01-15

5578
0
SQL
2014-01-30

有時候在設計系統時，會設計一個欄位來記錄多個選項的總合，例如使用者的權限等資訊。
這通常會在AP處理然後呈現出來，那如果要在DB中呈現的話，要如何處理呢?

有時候在設計系統時，會設計一個欄位來記錄多個選項的總合，例如使用者的權限等資訊。

這通常會在AP處理然後呈現出來，那如果要在DB中呈現的話，要如何處理呢?

以下以權限資料來測試，


USE tempdb
go

CREATE TABLE RightItems
(
Id INT IDENTITY
, RightName VARCHAR(30)
, RightFlag int
);

INSERT INTO RightItems(RightName, RightFlag) VALUES('Select', 1);
INSERT INTO RightItems(RightName, RightFlag) VALUES('Delete', 2);
INSERT INTO RightItems(RightName, RightFlag) VALUES('Update', 4);

CREATE TABLE Users
(
UserName VARCHAR(30)
, Rights INT
);
-- Rainmaker 可以Select, Delete, Update
INSERT INTO Users( UserName, Rights ) VALUES  ('Rainmaker', 7) 
-- Fred 可以Select 
INSERT INTO Users( UserName, Rights ) VALUES  ('Fred', 1) 

SELECT * FROM Users;

所以，要呈現出來的結果如下，

所以我們可以透過Function來幫我們展開來，如下，

2014/01/16 修改感謝 lbwshift2 先進指出錯誤所在，將原本 GetRightList Function 中，將 WHERE RightFlag = @StartIndex + 1 改成 WHERE RightFlag = @BitAndValue


create FUNCTION GetRightList( @ColumnValue INT )
RETURNS VARCHAR(30)
AS
BEGIN
	/*
	Select=>1
	Delete=>2
	Update=>4
	*/
	DECLARE @MaxFlagCount INT ,
		@StartIndex INT ,
		@Result VARCHAR(30) ,
		@BitAndValue INT,
		@CurrRight VARCHAR(30);
	SET @MaxFlagCount = 2;
	SET @StartIndex = 0;
	WHILE @StartIndex <= @MaxFlagCount
		BEGIN
			SET @BitAndValue = POWER(2, @StartIndex)
			--取得目前這個Flag的權限項目
			SELECT @CurrRight = RightName 
			from RightItems 
			WHERE RightFlag = @BitAndValue

			IF ( ( @ColumnValue & @BitAndValue ) = @BitAndValue )
					SET @Result = COALESCE(@Result + ',' + @CurrRight,@CurrRight);
			SET @StartIndex = @StartIndex + 1
		END
	RETURN @Result
END

所以可以透過 dbo.GetRightList Functio來將權限展開來，如下，


SELECT *, dbo.GetRightList(Rights) AS RightList
FROM Users


SELECT dbo.GetRightList(1) --Select
SELECT dbo.GetRightList(2) --Delete
SELECT dbo.GetRightList(3) --Select,Delete
SELECT dbo.GetRightList(4) --Update
SELECT dbo.GetRightList(5) --Select,Update
SELECT dbo.GetRightList(6) --Delete,Update
SELECT dbo.GetRightList(7) --Select,Delete,Update

結論

這種Bitwise的欄位操作可透過 POWER 函數來取得值然後進行 Bitwise 的操作，希望對大家有所幫助。

2014/01/30 補充 no limits but sky 大仔簡潔的作法，直接跟Flag值做And > 0 ( ( u.Rights & r.RightFlag ) > 0 )，如下，


SELECT DISTINCT
        u.*,
        STUFF(( SELECT  ', ' + r.RightName
                FROM    RightItems r
                WHERE   ( u.Rights & r.RightFlag ) > 0
              FOR
                XML PATH('')
              ), 1, 1, '') AS [RightName]
FROM    Users u;