巧用decode() 和 sign()_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2340 | 回复: 0   主题: 巧用decode() 和 sign()        下一篇 
nini
注册用户
等级:新兵
经验:56
发帖:63
精华:0
注册:2011-12-16
状态:离线
发送短消息息给nini 加好友    发送短消息息给nini 发消息
发表于: IP:您无权察看 2015-7-24 17:18:46 | [全部帖] [楼主帖] 楼主

1.DECODE(exp,s1,r1,s2,r2..s,r) 可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else的功能。
2.sign(exp) 判断正负数的,如果exp为负数则返回-1,为正数则返回1,为0则返回0;exp可以为number也可以为表达式。
这两个函数都是非常重要的函数也是比较常用的函数,估计大家都已经用过n次了,但是如果把这两个函数结合起来使用就能帮我们完成between and 的功能。
1.用a b c三个变量去实验b在这三个数中的位置(顺序)

SQL> var a number;
SQL> var b number;
SQL> var c number;
SQL> exec :a:=1;:b:=2;:c:=3;


PL/SQL 过程已成功完成。

SQL> select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'
大于等于',2,'最大') from dual;

DECODE(S
--------


中间

SQL> exec :a:=4;:b:=2;:c:=3;
;


PL/SQL 过程已成功完成。

SQL> select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'
大于等于',2,'最大') from dual;

DECODE(S
--------


最小

SQL> exec :a:=1;:b:=2;:c:=-1;
;


PL/SQL 过程已成功完成。

SQL> select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'
大于等于',2,'最大') from dual;

DECODE(S
--------


最大

SQL> exec :a:=2;:b:=2;:c:=3;
;


PL/SQL 过程已成功完成。

SQL> select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'
大于等于',2,'最大') from dual;

DECODE(S
--------


小于等于

SQL> exec :a:=1;:b:=2;:c:=2;
;


PL/SQL 过程已成功完成。

SQL> select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'
大于等于',2,'最大') from dual;

DECODE(S
--------


大于等于

SQL>


可见
sign(:b-:c)+sign(:b-:a)=-2时 b为三个数中最小,
sign(:b-:c)+sign(:b-:a)=-1时 b等于其中一个,小于另一个,
sign(:b-:c)+sign(:b-:a)=0时 b为三个数中中间的一个,
sign(:b-:c)+sign(:b-:a)=1时 b等于其中一个,大于另一个,
sign(:b-:c)+sign(:b-:a)=2时 b为三个数中最大,
这样实现between and 的功能就非常简单了
b between a and c 就等价于 decode(sign(:b-:c)+sign(:b-:a),0,XXX,-1,XXX,1,XXX).
也可以实现判断某个日期是否在某个时间段之间

select decode(sign(months_between(sysdate,sysdate-1)),1,XXX,0,XXXX,1,XXXX) from dual;
----实验脚本如下:

 var a number;


var b number;
var c number;
exec :a:=1;:b:=2;:c:=3;


select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'

大于等于',2,'最大') from dual;

var a number;
var b number;
var c number;
exec :a:=1;:b:=2;:c:=-1;


 select decode(sign(:b-:c)+sign(:b-:a),-2,'最小',-1,'小于等于',0,'中间',1,'

大于等于',2,'最大') from dual;

var a number;
var b number;
var c number;
exec :a:=1;:b:=2;:c:=-1;


select decode(:b,least(:a,:b,:c),'最小',GREATEST(:a,:b,:c),'最大','中间')

from dual;
var a number;
var b number;
var c number;
var d number;
exec :a:=1;:b:=2;:c:=-1;:d:=1;


select '从小到大,排位:' rank

from
(SELECT dense_rank() over (order by regexp_substr(:a ',' :b ',' :c ',' :d,'[^,]+',1,rownum)) rank,
regexp_substr(:a ',' :b ',' :c ',' :d,'[^,]+',1,rownum) num
FROM   dual
connect by  rownum <100 )
where :b=num;


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论