分组依据中的按位运算 [英] Bitwise operation in Group By
本文介绍了分组依据中的按位运算的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我必须在group by
查询中使用按位运算,但没有发现任何东西.
I must use bitwise operations in a group by
query but I didn't found anything.
表格:
PermissionId, BitMask(BigInt)
1, 4
2, 7
1, 8
1, 5
我希望结果为:
1, 13
2, 7
如何在T-SQL中如下编写该脚本
How can I write this script in T-SQL as below
SELECT PermissionId, BIT_OR(BitMask)
FROM table
GROUP BY PermissionId
推荐答案
您的问题变得非常有趣.
Your question just became very interesting.
创建此功能(您可能需要重新考虑名称)
Create this function(you may want to reconsider the name)
CREATE function f_test
(
@param bigint
)
returns @t table (value bigint)
AS
BEGIN
;WITH CTE AS
(
SELECT
@param % 2 value,
1 multiplier,
@param / 2 remain
UNION ALL
SELECT
remain % 2,
multiplier * 2,
remain / 2
FROM
CTE
WHERE
remain > 0
)
INSERT @t
SELECT multiplier
FROM CTE
WHERE value = 1
RETURN
END
现在您可以运行此脚本,我正在使用一个表变量,将其替换为您的表:
Now you can run this script, I am using a table variable, replace it with your table:
DECLARE @t table(PermissionId int, BitMask bigint)
INSERT @t values(1,4),(2,7),(1,8),(1,5)
SELECT
t1.PermissionId,
SUM(distinct t2.Value) Total
FROM
@t t1
CROSS APPLY
f_test(t1.BitMask) t2
GROUP BY
t1.PermissionId
结果:
PermissionId Total
1 13
2 7
这篇关于分组依据中的按位运算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文