SQL中的数据点整数按位值 [英] Pivot Integer Bitwise Values in SQL
问题描述
使用:SQL Server 2008 R2
Using: SQL Server 2008 R2
我的目标是取一个给定的整数(n),并在2列中选择n位整数的每个置换,如下所示.
My goal is to take a given integer (n) and select every permutation of integers with n bits in 2 columns, as below.
Given: n=4
Output:
ColA ColB
0 0
0 0
0 0
0 0
1 0
1 0
1 0
1 1
2 0
2 0
2 1
2 0
3 0
3 0
3 1
3 1
...
14 1
14 1
14 1
14 0
15 1
15 1
15 1
15 1
输出不会不是,重复不会,需要进行排序,这可能会或可能不会使此操作更容易.但是,我确实需要能够将n设置为任何整数...但是为了运行时,我们假设它将在10以下.
The output does not, repeat does NOT, need to be ordered, which may or may not make this easier. I do, however, need to be able to set n to any integer... but for the sake of runtime, let's say it'll be under 10.
提前谢谢.
推荐答案
您可能想尝试一下:
declare @bits int
set @bits = 4
;with AllTheNumbers as (
select cast (POWER(2, @Bits) as int) - 1 Number
union all
select Number - 1
from AllTheNumbers
where Number > 0
),
Bits as (
select @Bits - 1 Bit
union all
select Bit - 1
from Bits
where Bit > 0
)
select *, case when (Number & cast (POWER(2, Bit) as int)) != 0 then 1 else 0 end
from AllTheNumbers cross join Bits
order by Number, Bit desc
AllTheNumbers产生从0到2 ^ n-1的数字,Bits产生从0到@Bits-1的位数,并且主要部分连接它们并计算每个位置的位数.
AllTheNumbers produces the numbers from 0 to 2^n-1, Bits produces bit numbers from 0 to @Bits - 1 and main part connects them and calculates bit value per position.
这篇关于SQL中的数据点整数按位值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!