sql timestep 秒数后6位_有趣的SQL(一)

大家都想提高自己的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)，并@骑龟的兔子 就可与我联系。

扫码报名

2019.7.18 20:30

《如何制定SQL“减脂计划”》

END

---

知 数 堂

这里有好课，为职场助攻

免费兑换知数堂的课程和周边，戳此了解

扫码加入MySQL的技术Q群

(群号：579036588)