在子查询中汇总按位或 [英] Aggregate bitwise-OR in a subquery

问题描述

给出下表：

CREATE TABLE BitValues ( n int )

是否有可能为内的所有行计算 n 的按位或子查询？例如，如果BitValues包含以下4行：

Is it possible to compute the bitwise-OR of n for all rows within a subquery? For example, if BitValues contains these 4 rows:

+---+
| n |
+---+
| 1 |
| 2 |
| 4 |
| 3 |
+---+

我希望子查询返回7。有没有一种方法可以内联，不创建UDF ？

I would expect the subquery to return 7. Is there a way to do this inline, without creating a UDF?

推荐答案

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

这篇关于在子查询中汇总按位或的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！