数值函数
sign(n)函数,n为正数返回1,返回0,负数返回-1:
SELECT
SIGN
(-
20
),
SIGN
(
0
),
SIGN
(
'20'
)
FROM
DUAL;
结果如下: |
CEIL(n)返回大于等于输入参数的最小整数。 |
FLOOR(n)返回小于等于输入参数的最大整数。 |
sqrt:开方 |
power(n,m)返回n的m次方。 |
round(num,n)返回参数四舍五入之后得到的结果,n为正整数的时候,表示四舍五入到小数点第n位;n为负数的时候,表示四舍五入到小数点左边第n位。
SELECT
round
(
111.11111
,
4
),
round
(
111.11111
,-
1
),
round
(
111.11111
,
1
)
FROM
DUAL;
|
trunc(num,n)截取num,当n为正整数的时候,表示截取小数点后n为;n为负数的时候,表示截取小数点左边第n位。
SELECT
trunc
(
111.11111
,
4
),
trunc
(
111.11111
,-
1
),
trunc
(
111.11111
,
1
)
FROM
DUAL;
|
字符串函数
length和lengthb比较: length返回字符串长度,lengthb返回字节长度。
SELECT
length
(
'ab阳杨aa'
),
lengthb
(
'ab阳杨aa'
)
FROM
DUAL;
|
substr(str,index1,n)
取字符串str,位置index1后面的n个字符,index1取1或0都代表第一个字符。如果index1为负数,表示从右向左算。 |
instr(str,sub,dire,times)
获取字符串str,的子串sub的位置。dire取1代表从左到右找,dire取-1代表从右到左找。times代表找第几个。如果times超出范围,函数返回0。 |
replace(str1,str2,str3)函数: 返回str1中所有str2被str3替换之后的内容。 |
rpad(str1,n,str2)和lpad(str1,n,str2)函数: 字符串填充函数,str1用str2填充,直到字符串的长度为n。 两者区别:rpad填充str1右边,lpad填充str1左边。 例子:
SELECT
rpad
(
'test'
,
10
,
'*'
),
lpad
(
'test'
,
10
,
'*'
)
FROM
dual
|
trim()默认删除空格 |
日期函数
sysdate:没有参数,返回系统当前时间 |
systimestamp:没有参数,返回当前时间戳 |
add_months(date,integer)月份+integer: 例子:SELECT add_months(sysdate ,1) FROM dual |
last_day(date):返回指定月份的最后一天。 |
trunc(date,str):trunc除了用来截取数字之外,还能用来截取日期,截取日期的时候,str可以是'yyyy'|'mm'|'dd'|'hh24'|'mi'|'q'('q'表示季度),默认'dd',并且返回的是时间不是字符串。 例如:SELECT sysdate ,trunc( sysdate,'mi' ),case when sysdate >trunc( sysdate,'mi' ) then 1 else 0 end iscompare FROM dual; 输出: 例如:SELECT sysdate,trunc (sysdate, 'yyyy'),trunc (sysdate, 'mm'),trunc (sysdate, 'dd'),trunc (sysdate, 'hh24'),trunc (sysdate, 'mi') FROM dual; 输出: |
转换函数
bin_to_num(date...)二进制转十进制:
SELECT
bin_to_num(
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
,
1
)
FROM
dual
|
to_char:数字、日期转字符串,格式化数字 |
to_number:字符串转数字 |
to_date:字符串转日期('yyyy-MM-dd HH24:mi:ss') |
null函数
coalesce(expr):返回第一个不为null的表达式结果。
SELECT
coalesce
(
null
,
null
,
1
,
null
)
FROM
dual;
|
NVL( string1, replace_with)函数:
如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值。
引申一下,此NVL的作用与SQLserver 中的 ISNULL( string1, replace_with) 一样。
|
分析函数
rollup()函数:放在group by后面,除了按分组使用集合函数外,还会按照参数顺序分别求和。 例子:
select
earnmonth, area,
sum
(personincome)
from
earnings
group
by
rollup
(earnmonth,area);
输出:以上红框部分是对应求和结果,先按Area求和,再按earnmonth求和。 |
cube()函数:和rollup函数差不多,但是比rollup函数更全面,会根据括号中所有参数的组合数进行分组。 |
rollup和cube区别:
如果是ROLLUP(A, B, C)的话,GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C),GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A),
(B、C)
(B)
(C),
最后对全表进行GROUP BY操作。
|
grouping函数 在以上例子中,是用rollup和cube函数都会对结果集产生null,这时候可用grouping函数来确认该记录是由哪个字段得出来的grouping函数用法,带一个参数,参数为字段名,结果是根据该字段得出来的就返回1,反之返回0 例子:
select
decode
(
grouping
(earnmonth),
1
,
'所有月份'
, earnmonth) 月份,
decode
(
grouping
(area),
1
,
'全部地区'
, area) 地区,
sum
(personincome) 总金额
from
earnings
group
by
cube
(earnmonth, area)
order
by
earnmonth, area
nulls
last
;
输出:nulls last 是将空值放到最后。 |
其他函数
decode(语句,值,返回值1,返回值2),如果”语句==值“返回“返回值1”否则返回“返回值2”。
1通常我们这么写:
2
3 进行数字的行列转换
| |
WMSYS.WM_CONCAT()
聚集函数将多行结果用逗号隔开,以一行的形式展现出来.
| |
nullif(ex1,ex2):
值相等返空,否则返回第一个值 例:如果工资和佣金相等,则显示空,否则显示工资 select nullif(sal,comm),sal,comm from emp; | |
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
| |
分类统计 (并显示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,b,c,sum(c) over(partition by a) from t2
得到结果:
A B C SUM(C)OVER(PARTITION BY A)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
over (partition by col1) 和 group by 不同的是,over (partition by col1)不会将相同的元组合并,但是group by会。
| |
lag( EXPRESSION ,<OFFSET>,<DEFAULT>) over(order by col)和lead( EXPRESSION ,<OFFSET>,<DEFAULT>) over(order by col): lag取前OFFSET行的数据,lead取后OFFSET行的数据。 例子:
with
tmp
as
((
select
'杨焕滨'
as
name
,
1
mark
FROM
DUAL)
union
all
(
select
'杨焕滨'
as
name
,
2
mark
FROM
DUAL)
union
all
(
select
'杨焕滨'
as
name
,
3
mark
FROM
DUAL)
union
all
(
select
'杨焕滨'
as
name
,
4
mark
FROM
DUAL)
union
all
(
select
'杨焕滨'
as
name
,
5
mark
FROM
DUAL)
union
all
(
select
'杨晓滨'
as
name
,
1
mark
FROM
DUAL)
union
all
(
select
'杨晓滨'
as
name
,
2
mark
FROM
DUAL)
union
all
(
select
'杨晓滨'
as
name
,
3
mark
FROM
DUAL)
union
all
(
select
'杨晓滨'
as
name
,
4
mark
FROM
DUAL)
union
all
(
select
'杨晓滨'
as
name
,
5
mark
FROM
DUAL))
SELECT
NAME
,MARK,
LAG
(MARK,
3
,
0
)
OVER
(
ORDER
BY
NAME
,MARK)
FROM
TMP;
输出: |