问题描述
作为复杂聚合的一部分,我想知道一些数据的按位总和,即如果我有值为 1,1,1,2,2,8 的行,按位 sum 是 11.在这种情况下,这些值都是 2 的精确幂(单个位),因此我可以通过对组进行分组和求和来绕过它(显然,与实际查询相比,这个示例有点受折磨)):
As part of a complex aggregate I want to know the bitwise sum of some data, i.e. if I have rows with values 1,1,1,2,2,8 the bitwise sum is 11. In this case the values are all exact powers of two (single bits), so I can hack around it by grouping and summing over the groups (obviously this example is a bit tortured compared to the real query):
select SUM(y.test) from ( select x.test from ( -- garbage test data select 1 as [test] union all select 1 union all select 1 union all select 2 union all select 2 union all select 8) x group by x.test) y
但是有没有一种干净的方法可以在 [T]SQL 中执行按位求和?
but is there a clean way to perform a bitwise sum in [T]SQL?
推荐答案
如果您的所有测试值都是单个位,如您的示例 (1, 2, 8) - 只需使用 SUM(DISTINCT col) 在您的查询中.
If all of your test values are single bits as in your example (1, 2, 8) - simply use SUM(DISTINCT col) in your query.
希望有所帮助.
(参考:http://www.51sjk.com/Upload/Articles/1/0/333/333384_20221021152604320.aspx)