大家都想提高自己的SQL能力,但是SQL能力也是需要慢慢提高的,为了让大家的SQL有所提高,特整理了下面的SQL。
需求:如下所示,有从1开始的2的幂的数组
- - 1, 2, 4, 8, 16, 32, 64, 128
想求出如下结果,如,输入17 在上面的数组中应得出
例:
输入值 17 : 1 + 16
输入值 31 : 1 + 2 + 4 + 16
输入值 32 : 32
想实现如上功能,首先我们需要得出比起特定输入值小的特定数组
这里我们用Oracle 提供的connect by 来实现
SQL> select level from dual connect by level < 17;
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
16 rows selected.
从上的SQL中我们已经实现了,但是我们需要的是:有从1开始的2的幂的数组
也是就是 POWER(2, LEVEL - 1)
SQL> select level ,POWER(2, LEVEL - 1) x from dual connect by level < 17;
LEVEL X
---------- ----------
1 1
2 2
3 4
4 8
5 16
6 32
7 64
8 128
9 256
10 512
11 1024
12 2048
13 4096
14 8192
15 16384
16 32768
16 rows selected.
但是行数16行显然有很多没必要的值,我们需要加大跳跃步数
SQL> select 17 ,POWER(2, LEVEL - 1) x from dual connect by POWER(2, LEVEL - 1) <= 17;
17 X
---------- ----------
17 1
17 2
17 4
17 8
17 16
现在已经求出了 如上述所示的结果 , 现在我们就剩下怎样把1,16 得出
Oracle 给我们提供了 BITAND 函数,是用来比较两个二进制数与运算(AND运算)
下面是 网上查到的 bitand 函数的用法和案例
bitand( ) 函数
返回两个数值型数值在按位进行 AND 运算后的结果。
语法
BITAND(nExpression1, nExpression2)
参数
nExpression1, nExpression2
指定按位进行 AND 运算的两个数值。如果 nExpression1 和 nExpression2 为非整数型,那 么它们在按位进行 AND 运算之前转换为整数。
返回值类型
数值型
说明 BITAND( ) 将 nExpression1 的每一位同 nExpression2 的相应位进行比较。如果 nExpression1 和 nExpression2 的位都是 1,相应的结果位就是 1;否则相应的结果位是 0。
下表列出对 nExpression1 和 nExpression2 按位进行 AND 运算的结果:
nExpression1 位 nExpression2 位 结果位
bitand( ) 函数示例
x = 5 && 二进制为 0101
y = 6 && 二进制为 0110
bitand(x,y) && 返回值 4,二进制为 0100
SQL> select 17 ,POWER(2, LEVEL - 1) x ,bitand(17,POWER(2, LEVEL - 1) ) xy from dual connect by POWER(2, LEVEL - 1) < 17;
17 X XY
---------- ---------- ----------
17 1 1
17 2 0
17 4 0
17 8 0
17 16 16
现在的情况下只要求出大于0的部分就可以了
SQL> select x from (
2 select 17 ,POWER(2, LEVEL - 1) x ,bitand(17,POWER(2, LEVEL - 1) ) xy from dual connect by POWER(2, LEVEL - 1) <= 17
3 )where xy >0;
X
----------
1
16
整理之后如下
SQL> SELECT x
2 , POWER(2, LEVEL - 1) y
3 FROM (SELECT 17 x FROM dual)
4 WHERE BITAND(x, POWER(2, LEVEL - 1)) > 0
5 CONNECT BY POWER(2, LEVEL - 1) <= x;
X Y
---------- ----------
17 1
17 16
SQL> SELECT x
2 , POWER(2, LEVEL - 1) y
3 FROM (SELECT 31 x FROM dual)
4 WHERE BITAND(x, POWER(2, LEVEL - 1)) > 0
5 CONNECT BY POWER(2, LEVEL - 1) <= x;
X Y
---------- ----------
31 1
31 2
31 4
31 8
31 16
SQL>
SQL> SELECT x
2 , POWER(2, LEVEL - 1) y
3 FROM (SELECT 32 x FROM dual)
4 WHERE BITAND(x, POWER(2, LEVEL - 1)) > 0
5 CONNECT BY POWER(2, LEVEL - 1) <= x
6 ;
X Y
---------- ----------
32 32
虽然这种SQL 比较复杂,但是因为有网络的存在我们可以搜集类似这样的SQL,万一以后我们能用的上呢。
谢谢大家, 欢迎转发!
我是知数堂SQL优化班老师-郑松华
如有关于SQL优化方面疑问需要交流的,请加入QQ群(579036588),并@骑龟的兔子 就可与我联系。
![4d2e1b069910f13d891cc2f7a0b0cd8e.png](https://i-blog.csdnimg.cn/blog_migrate/9bd4155d284142a987be34930aec01fb.png)
扫码报名
2019.7.18 20:30
《如何制定SQL“减脂计划”》
END
这里有好课,为职场助攻
免费兑换知数堂的课程和周边,戳此了解
![11a9c8186615651b735c116ca83f2c6c.gif](https://i-blog.csdnimg.cn/blog_migrate/813b1928a09adea484866583cb655659.gif)
扫码加入MySQL的技术Q群
(群号:579036588)