二进制位域汇总OR值 [英] Aggregate bitfield values with binary OR

问题描述

我有一个表的 INT 的值被用作位域（其中每个位是标志）。

I have a table with int values being used as bitfields (where each bit is a flag).

现在我想与二元运算聚集它们（在我的情况OR），因此：

Now I would like to aggregate them with a binary operation (in my case OR) so that:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

SELECT AND_AGGR(bitfield) -- Invalid because AND_AGGR doesn't exist
FROM #TABLE

DROP #TABLE

将导致价值 171

什么是做一个很好的方式，希望并不需要很多的 | 和 MAX （但如果你一定要，你必须）？

What would be a good way to do this that hopefully doesn't require a lot of | and MAX (but if you must, you must)?

我使用MS SQL Server 2008的自己，但在其他的服务器解决方案的兴趣也。

I am using MS SQL Server 2008 myself, but solutions on other servers are also of interest.

推荐答案

如果你期待的结果 171 ，想必你的意思是二进制或不是和？

If you're expecting the result 171, surely you mean binary OR not AND?

在任何情况下，该溶液聚合中的值到一个变量：

In any case, this solution aggregates the values into a variable:

SELECT 1 AS bitfield
INTO #TABLE
UNION ALL SELECT 1 + 2 + 8 + 32
UNION ALL SELECT 2 + 128
UNION ALL SELECT 2 + 32

DECLARE @i int = 0

SELECT @i = @i | bitfield
FROM #TABLE

SELECT @i

DROP TABLE  #table

如果您希望通过组另一场聚集这可能不符合你的要求。

This might not meet your requirements if you want to group the aggregation by another field.

这也是不太可能在一个大表表现良好。

It is also unlikely to perform well on a large table.

这篇关于二进制位域汇总OR值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！