在子查询中聚合按位或

本文介绍了在子查询中聚合按位或的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

给定下表:

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