像C#枚举标志一样的SQL Server按位处理 [英] SQL Server Bitwise Processing like C# Enum Flags
问题描述
如何在SQL Server中使用标志的处理，例如C#中的枚举?
How can one use in SQL Server the processing of the Flags such as on enums in C#?
例如，一个人如何返回一个属于列表或类似条件的用户列表:
For example, how would one return a list of users that are part of a list or conditions like so:
ConditionAlpha = 2
ConditionBeta = 4
ConditionGamma = 8
...
然后会有一些对他们不利的用户，就像这样:
Then there will be users with some of these conditions against them like so:
User1: 6 (conditions Alpha and Beta)
User2: 4 (condition Beta)
User3: 14 (conditions Alpha, Beta and Gamma)
...
我们希望能够进行一个查询，即让所有具有第一个条件Alpha的用户得到查询，在这种情况下，即使他们也具有其他条件，它也会返回用户1和3.
We want to be able to do a query where we say get all users with the first condition Alpha and in this scenario it would return users 1 and 3 even though they have other conditions as well.
推荐答案
检查SQL中是否设置了标志的按位运算符是& . WHERE 子句需要计算为 BOOLEAN 表达式，如下所示:
The bitwise operator for checking whether a flag is set in SQL is &. The WHERE clause needs to evaluate to a BOOLEAN expression, like this:
create table #temp (id int, username varchar(20), flags int)
insert into #temp values
(1, 'User1', 6 /* (2 | 4) */),
(2, 'User2', 4),
(3, 'User3', 14 /* (2 | 4 | 8) */)
declare @ConditionOne int = 2
select *
from #temp
where flags & @ConditionOne <> 0
declare @ConditionTwo int = 4
select *
from #temp
where flags & @ConditionTwo <> 0
declare @ConditionThree int = 8
select *
from #temp
where flags & @ConditionThree <> 0
drop table #temp
这些查询返回以下结果集:
These queries return the following resultsets:
id username flags
----------- -------------------- -----------
1 User1 6
3 User3 14
id username flags
----------- -------------------- -----------
1 User1 6
2 User2 4
3 User3 14
id username flags
----------- -------------------- -----------
3 User3 14
这篇关于像C#枚举标志一样的SQL Server按位处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
