一、
row_number() over
()
资料甲
1 select column_name
1
,column_name,data_type,
2 row_number() over (partition by column_name
1
order by column_name) row_num
3 * from test_col where rownum < 20
partition by
相当与
Group by
,如果有了
Partition by
则
Oralce
会先分
组
,且
Row_number
是以
每
一
组为单
位,且在一
组
内
进
行
标识顺
序的
可以使用
partition by
字句
,
这样
row_number
就分
类
做出排名
如果不指定
partition
就相当于在
order by
的字句上
进
行排序了
资料乙
"ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组,在分组内部根据 COL2排序
而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)
RANK()
类似,不过RANK 排序的时候跟派名次一样,
可以并列2个第一名之后 是第3名LAG 表示 分组排序后 ,
组内后面一条记录减前面一条记录的差,
第一条可返回 NULL BTW: EXPERT ONE ON ONE
上讲的最详细,还有很多相关特性,
文档看起来比较费劲row_number()和rownum差不多
,功能更强一点(可以在各个分组内从1开时排序)rank()是跳跃排序
,有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()l是连续排序,
有两个第二名时仍然跟着第三名。
相比之下row_number是没有重复值的 lag(arg1,arg2,arg3):
arg1
是从其他行返回的表达式
rg2
是希望检索的当前行分区的偏移量。
是一个正的偏移量,时一个往回检索以前的行的数目。
arg3
是在arg2表示的数目超出了分组的范围时返回的值。 "
二、NVL()
NVL (expr1, expr2)
如果expr1为NULL,返回expr2
NVL2 (expr1, expr2, expr3)
如果expr1为非空,则返回expr2,如果expr1为NULL,则返回expr3
NULLIF (expr1, expr2)
如果expr1=expr2,返回NULL,否则返回expr1
三、CASE WHEN THEN
语法:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
在简单的CASE表达式中,ORACLE会搜索expr 等于comparison_expr的第一对when then,然后返回return_expr.如果没有满足条件的when then 并且存在ELSE子句,那么ORACLE会返回else_expr,否则会返回空值。
您不能为所有这些return_expr和else_expr指定文字的NULL。
四、DECODE()
DECODE
有什
么
用途呢?
先构造一个例子,假
设
我
们
想
给
智星
职员
加工
资
,其
标
准是:工
资
在
8000
元以下的将加20%;工
资
在
8000
元以上的加15%,通常的做法是,先
选
出
记录
中的工
资
字段
值
? select salary into var-salary from employee
,然后
对变
量
var-salary
用if-then-else或choose case之
类
的流
控制
语
句
进
行判断。
如果用DECODE函数,那
么
我
们
就可以把
这
些流控制
语
句省略,通
过
SQL
语
句就可以直接完成。如下:
select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee
是不是很
简洁
?
DECODE
的
语
法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
,表示如果value等于if1
时
,
DECODE
函数的
结
果返回
then1,...,
如果不等于任何一个if
值
,
则
返回
else
。初看一下,DECODE 只能做等于
测试
,但
刚
才也看到了,我
们
通
过
一些函数或
计
算替代
value
,是可以使DECODE函数具
备
大于、小于或等于功能。
decode()函数使用技巧
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
· 使用方法:
1、比
较
大小
select decode(sign(
变
量
1-
变
量
2),-1,
变
量
1,
变
量
2) from dual; --
取
较
小
值
sign()函数根据某个
值
是
0
、正数
还
是
负
数,分
别
返回
0
、1、-1
例如:
变
量
1=10
,
变
量
2=20
则
sign(
变
量
1-
变
量
2)
返回-1,decode解
码结
果
为
“
变
量
1”
,达到了取
较
小
值
的目的。
五、||
Oracle
用|| 符号作为连接符, Oracle查询如下所示:
Select ‘Name’ || ‘Last Name’ From tableName
查询结果中name和last name连接在一起了
六、CONCAT()
拼接
CONCAT('Good', 'String') GoodString
七、LENGTH()
求长度
LENGTH('String') 6
八、TO_CHAR()
TO_CHAR
将数字或日期转换为字符串
TO_CHAR(date, 'fmt')
TO_CHAR(date, 'fmt')
格式串包含在
''
中
,
大小写敏感
,
可以是任意有效的日期
格式
格式串:
YYYY
表示完整的四位数字年
YEAR
表示英文拼写的年
MM
表示两位数字月
MONTH
表示英文全拼的年
DY
表示三位
缩
写的星期
DAY
表示英文全拼的星期
一些特殊用法:
时间
格式
HH24:MI:SS AM 15:45:32 PM
在格式串中加入字符串
DD " of " MONTH 12 of OCTOBER
用后
缀
拼出整个日期
ddspth fourteenth
TO_CHAR(number, 'fmt')
使用此函数将数字
转换
成数字
9
表示数字
0
强
制
为
0
$
设
置美元符号
L
使用当前字符集的
货币
符号
.
小数点
,
千位分隔符