按位或在子查询中聚合

WITH    Bits
          AS ( SELECT   1 AS BitMask
               UNION ALL
               SELECT   2
               UNION ALL
               SELECT   4
               UNION ALL
               SELECT   8
               UNION ALL
               SELECT   16
             )
    SELECT  SUM(DISTINCT BitMask)
    FROM    ( SELECT    1 AS n
              UNION ALL
              SELECT    2
              UNION ALL
              SELECT    3
              UNION ALL
              SELECT    4
              UNION ALL
              SELECT    5
              UNION ALL
              SELECT    6
            ) AS t
            JOIN Bits ON t.n & Bits.BitMask > 0

一个简单的解决方案,它混合了@alexkuznetsov和@andomar的解决方案。
位掩码是由递归公共表表达式生成的,但其方法比@andomar的解决方案简单。
然后像在@alexkuznetsov的解决方案中一样求和位。
在这个例子中,我假设需要一个16位的掩码,因此是65536的限制。您可以通过将65536更改为2^n来指示n位掩码。

WITH Bits AS
(
    SELECT 1 BitMask
    UNION ALL
    SELECT 2 * BitMask FROM Bits WHERE BitMask < 65536 -- recursion
)
SELECT SUM(DISTINCT BitMask)
FROM
    (SELECT 1 n
    UNION ALL
    SELECT 2 n
    UNION ALL
    SELECT 4 n
    UNION ALL
    SELECT 3 n) t
    INNER JOIN Bits ON t.n & Bits.BitMask > 0

我看到这篇文章很老,有一些有用的答案,但这是一个非常疯狂的直接方法…

Select  
    SUM(DISTINCT(n & 0x01)) +
    SUM(DISTINCT(n & 0x02)) +
    SUM(DISTINCT(n & 0x04))
    as OrN
From BitValues

准备工作:

if object_id(N'tempdb..#t', N'U') is not null drop table #t;
create table #t ( n int );
insert into #t values (1), (2), (4), (3);

解决方案:

select max(n & 8) + max(n & 4) + max(n & 2) + max(n & 1) from #t;

可以使用变量并执行“按位或”( | )每行:

declare @t table (n int)
insert @t select 1 union select 2 union select 4

declare @i int
set @i = 0

select  @i = @i | n
from    @t

select @i

这张照片 7 . 注意,在select中分配变量并不是官方支持的。

以更严格的SQL方式,您可以为每一位创建一个具有一行的表。这个表有31行,因为第32位是负整数。此示例使用递归CTE创建该表:

declare @t table (n int)
insert @t select 1 union select 2 union select 3

; with bits(nr, pow) as 
(
    select  1
    ,       1
    union all
    select  nr + 1
    ,       pow * 2
    from    bits
    where   nr <= 30
)
select  sum(b.pow)
from    bits b
where   exists
        (
        select  *
        from    @t t  
        where   b.pow & t.n > 0
        )

这将对源表中的任何位进行求和。

我尝试使用coalesce函数,但它起作用,例如:

DECLARE @nOrTotal INT

SELECT @nOrTotal = COALESCE(@nOrTotal, 0) | nValor 
    FROM (SELECT 1 nValor
              UNION 
          SELECT 2
              UNION 
          SELECT 2) t

SELECT @nOrTotal

>> Result: 3

这是另一种选择,没有(欢呼!!!!):

    select sum(distinct isnull(n & BitMask, 0)) as resultvalue
    from 
    (
          SELECT    1 AS n
          UNION ALL
          SELECT    2
          UNION ALL
          SELECT    4
          UNION ALL
          SELECT    3
    ) t
    INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
    ON n & BitMask = BitMask;

还要考虑一个分组示例:

 -- Setup temp table to produce an example --
 create table #BitValues
 (
    id int identity(1,1)
    ,value int
    ,groupby varchar(10)
 )

 insert into #BitValues
 SELECT    1 AS value, 'apples'
          UNION ALL
          SELECT    2, 'apples'
          UNION ALL
          SELECT    4, 'apples'
          UNION ALL
          SELECT    3, 'apples'

 -- Bit operation: --
  select groupby, sum(distinct isnull(value & BitMask, 0)) as tempvalue
  from #BitValues
  INNER JOIN (SELECT 0 BitMask union all SELECT 1 union all SELECT 2 union all SELECT 4 union all SELECT 8 union all SELECT 16 union all SELECT 32 union all SELECT 64 union all SELECT 128 union all SELECT 256 union all SELECT 512 union all SELECT 1024 union all SELECT 2048 union all SELECT 4096 union all SELECT 8192 union all SELECT 16384 union all SELECT 32768 union all SELECT 65536) Bits -- = SELECT POWER(2, 16)
      ON value & BitMask = BitMask
  group by groupby

第一个例子意味着要比用慢。但是,当您将groupby与其他一些数据一起使用时,查询的成本基本相同。

另一种方法是

    select 
    groupby
      ,max(case when n & 1 = 1 then 1 else 0 end)
            +
        max(case when n  & 2 = 2 then 2 else 0 end)
            +
        max(case when n & 4 = 4 then 4 else 0 end)  
            +
        max(case when n & 8 = 8 then 8 else 0 end)
            +
        max(case when n & 16 = 16 then 16 else 0 end)
            +
        max(case when n & 32 = 32 then 32 else 0 end)
            +
        max(case when n & 64 = 64 then 64 else 0 end)
            +
        max(case when n & 128 = 128 then 128 else 0 end)
            +
        max(case when n & 256 = 256 then 256 else 0 end)
            +
        max(case when n & 512 = 512 then 512 else 0 end)
            +
        max(case when n & 1024 = 1024 then 1024 else 0 end)
            as NewDNC
    from #BitValues
    group by groupby;

更糟的是,由于代码重复,可读性更高,执行成本也差不多。

你在找这样的东西吗?

编辑 :如其他注释所述,此答案基于以下假设:位值表只包含2的幂。我尝试在问题行之间进行阅读,并推断出对内联子查询的使用。

declare @BitValues table (
    n int
)

declare @TestTable table (
    id int identity,
    name char(10),
    BitMappedColumn int
)

insert into @BitValues (n)
    select 1 union all select 2 union all select 4

insert into @TestTable
    (name, BitMappedColumn)
    select 'Joe', 5 union all select 'Bob', 8

select t.id, t.name, t.BitMappedColumn
    from @TestTable t
        inner join (select SUM(n) as BitMask from @BitValues) b
            on t.BitMappedColumn & b.BitMask <> 0

对我来说,这是最好的解决办法。

declare @res int
set @res=0    
SELECT  @res=@res|t.n
    FROM    ( SELECT    1 AS n
              UNION ALL
              SELECT    2
              UNION ALL
              SELECT    3
              UNION ALL
              SELECT    4
              UNION ALL
              SELECT    5
              UNION ALL
              SELECT    6
            ) AS t

对于可读和可重用的解决方案,最好的选择是编写一个自定义的clr聚合来执行按位或。有关创建此类型操作的教程,请参见: http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx