本文介绍了在子查询中聚合按位或的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
给定下表:
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 >0Given 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
更多推荐
在子查询中聚合按位或
发布评论