問題描述

我有一個(gè)安全表，其中包含一個(gè)組和用戶列表，每個(gè)組和用戶都具有按位整數(shù)權(quán)限.對(duì)于每個(gè)給定的用戶，我想對(duì)他們的所有組和他們的個(gè)人權(quán)限記錄(如果存在)執(zhí)行按位 AND.

I have a security table containing a list of groups and users with a bitwise integer permission for each. For each given user, I would like to perform a bitwise AND on all of their groups and of their personal permission record, if present.

當(dāng)然，我可以在我的代碼中輕松地做到這一點(diǎn)，但我更愿意在數(shù)據(jù)庫(kù)中做到這一點(diǎn)，因?yàn)榭赡苡袛?shù)以千計(jì)的項(xiàng)目我正在查詢其權(quán)利.

Of course, I can easily do this in my code, but I'd rather do it in the database as there could be thousands of items I am querying the rights for.

相比游標(biāo)，我更喜歡基于集合的解決方案.

I would prefer a set-based solution over a cursor.

請(qǐng)注意，我無(wú)法控制架構(gòu).

Note than I do not have control over the schema.

推薦答案

如果您想要按位或單位值的所有值，基于集合的解決方案是可能的:按位求和

A set-based solution is possible if all the values that you want to bitwise or are single-bit values: Performing a bitwise sum

或者，您可以使用不太優(yōu)雅的方法對(duì)非唯一值集進(jìn)行基于模糊集的按位運(yùn)算:

Alternatively, you can use a less-elegant method for vaguely-set-based bitwise operations on sets of non-unique values:

DECLARE @BitSum INT
SET @BitSum = 0
SELECT @BitSum = @BitSum | BitValue
FROM (
    SELECT 1 AS BitValue
    UNION SELECT 7
    UNION SELECT 16
) AS SampleValues
SELECT @BitSum

Hugo Kornelis 在另一篇文章中非常全面地回答了這個(gè)問題:http://www.eggheadcafe.com/software/aspnet/33139293/b??itwise-aggregate-function.aspx

Hugo Kornelis answers the question pretty comprehensively in this other post: http://www.eggheadcafe.com/software/aspnet/33139293/bitwise-aggregate-function.aspx

這篇關(guān)于我可以在 Sql Server 中對(duì)一組數(shù)字執(zhí)行按位和嗎?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！