sql------- decode()， trunc()，(+),sign()

来源：互联网发布：淘宝ab单试用平台编辑：程序博客网时间：2024/05/16 13:44

decode()函数简介：

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

使用方法：

Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）

From talbename

Where …

其中columnname为要选择的table中所定义的column，

·含义解释：

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：

if （条件==值1）

then　　　　

return(翻译值1)

elsif （条件==值2）

then　　　　

return(翻译值2)　　　　

......

elsif （条件==值n）

then　　　　

return(翻译值n)

else　　　　

return(缺省值)

end if

注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；

举例说明：

现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other；

SQL如下：

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output

特殊情况：

若只与一个值进行比较

Select monthid ,decode（sale， NULL，‘---’，sale） sale from output

另：decode中可使用其他函数，如nvl函数或sign（）函数等；

NVL(EXPR1,EXPR2)

若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.

SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;

如果用到decode函数中就是

select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，

如果取较小值就是

select monthid,decode(sign(sale-6000),-1,sale,6000) from output，即达到取较小值的目的。

Oracle trunc()函数的用法:

/**************日期********************/
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

oracle各种连接介绍:
SQL> select * from a1;
BBB
----------
101
102
103
104
105

SQL> select * from a2;
BBB        CCC
---------- --------------------
101
102
105

SQL> select * from a1,a2 where a1.bbb(+)=a2.bbb;  右连接

BBB        BBB        CCC
---------- ---------- --------------------
101        101
102        102
105        105

SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);  左连接

BBB        BBB        CCC
---------- ---------- --------------------
101        101
102        102
103
104
105        105

再一个例子
a：
id       name
6 D
1 A
2 B
3 C

b:
id       name
1 10
2 20
3 30
5 40
右连接：
SQL> select a.id,a.name,b.id,b.name from a, b WHERE a.id(+) = b.id;

ID  NAME                           ID  NAME
--- ------------------------------ --- --------------------
1   A                              1   10
2   B                              2   20
3   C                              3   30
                                   5   40
左连接
SQL> select a.id,a.name,b.id,b.name from a, b WHERE a.id = b.id(+);

ID  NAME                           ID  NAME
--- ------------------------------ --- --------------------
1   A                              1   10
2   B                              2   20
3   C                              3   30
6   D

右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，左连接与之相反

a(+)=b:右连接“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配。
a=b(+):左连接

内连接
SQL> select * from a inner join b on a.id=b.id;
ID  NAME                           QQQ ID  NAME
--- ------------------------------ --- --- --------------------
1   A                              1   1   10
2   B                              1   2   20
3   C                              1   3   30
相当于select a.*,b.* from a,b where a.id=b.id

外连接

SQL> select * from a left join b on a.id=b.id;

ID  NAME                           QQQ ID  NAME
--- ------------------------------ --- --- --------------------
1   A                              1   1   10
2   B                              1   2   20
3   C                              1   3   30
6   D                              1

内外连接只有在9i以后才能使用

补充
外部联接 "+" 按其在 "=" 的左边或右边分左联接和右联接 . 若不带 "+" 运算符的表中的一个行不直接匹配于带 "+" 预算符的表中的任何行 , 则前者的行与后者中的一个空行相匹配并被返回 . 若二者均不带 '+', 则二者中无法匹配的均被返回 . 利用外部联接 "+", 可以替代效率十分低下的 not in 运算 , 大大提高运行速度 . 例如 , 下面这条命令执行起来很慢

select a.empno from emp a where a.empno not in

(select empno from emp1 where job='SALE');

---- 倘若利用外部联接 , 改写命令如下 :

select a.empno from emp a ,emp1 b

where a.empno=b.empno(+)

and b.empno is null

and b.job='SALE';

oracle中sign函数详解:

在Oracle/PLSQL中, sign 函数返回一个数字的正负标志.

语法如下：sign( number )

number 要测试标志的数字.

If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.

应用于:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

例如:

sign(-23)

would return -1

sign(-0.001)

would return -1

sign(0)

would return 0

sign(0.001)

would return 1

sign(23)

would return 1

sig(23.601)

would return 1

示例：
一、select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

　　SIGN(100) SIGN(-100) SIGN(0)
　　-----------------------------------------------------------
　　1 -1 0

二、a=10，b=20
　　则sign(a-b)返回-1

阅读全文

0 0