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

问题描述

给定下表:

CREATE TABLE BitValues ( n int )

是否可以为子查询中的所有行计算n的按位或??例如，如果 BitValues 包含这 4 行:

<前>+---+|| |+---+|1 ||2 ||4 ||3 |+---+

我希望子查询返回 7.有没有办法在不创建 UDF 的情况下内联执行此操作?

解决方案

WITH BitsAS ( 选择 1 AS 位掩码联合所有选择 2联合所有选择 4联合所有选择 8联合所有选择 16)SELECT SUM(DISTINCT BitMask)从 ( 选择 1 作为 n联合所有选择 2联合所有选择 3联合所有选择 4联合所有选择 5联合所有选择 6) AS tJOIN Bits ON t.n &Bits.BitMask >0

Given the following table:

CREATE TABLE BitValues ( n int )

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 |
+---+

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屋！