--Oracle常用函数
-- 字符函数,dual是Oracle中的一个测试函数
-- 求字符串的长度
select length('ABCD') from dual;
-- 求字符串的子串,截取位置是以1开始
select substr('ABCD',2,2) from dual;
-- 字符串拼接
select concat('AB','CD') from dual; -- 不常用
select
-- 数值函数,四舍五入
select round(100.57) from dual;
-- 截取函数
select trunc(100.45) from dual;
select trunc(100.23343,2) from dual;
--取模函数
select mod(10,3) from dual;
-- 日期函数,默认截取日
select trunc(sysdate) from dual;
-- 按年截取,得到一年的第一天
select trunc(sysdate,'yyyy') from dual;
-- 按月截取,得到一个月的第一天
select trunc(sysdate,'MM') from dual;
-- 转换函数
-- 数字转换成字符串
select to_char(1024) from dual;
-- 日期转换成字符串
select to_char(sysdate,'yyyy-MM-dd') from dual;
-- 字符换日期
select to_date('2017-12-21','yyyy-MM-dd') from dual;
-- 其他函数(重要)
-- 空值函数
select NVL(null,0) from dual;
-- 显示价格表中业主类型id为1的价格记录,如果上限值为null,则显示99999
select money, num0,nvl(num1,99999) from t_account where ownertypeid=1;
-- 空值处理函数2
select money, num0,nvl2(num1,to_char(num1),'不限') from t_account where ownertypeid=1;
--显示下列信息(不要关联业主查询列表,直接判断1,2,3的值)
select name ,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') 类型 from t_owners;
select name,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其他'
end
) from t_owners;
-- 按月份统计2012年各个地区的水费
select (select name from t_area where id=areaid) ar,
sum( case when month='1' then money else 0 end)一月,
sum( case when month='2' then money else 0 end)二月,
sum( case when month='3' then money else 0 end)三月,
sum( case when month='4' then money else 0 end)四月,
sum( case when month='5' then money else 0 end)五月,
sum( case when month='6' then money else 0 end)六月,
sum( case when month='7' then money else 0 end)七月,
sum( case when month='8' then money else 0 end)八月,
sum( case when month='9' then money else 0 end)九月,
sum( case when month='10' then money else 0 end)十月,
sum( case when month='11' then money else 0 end)十一月,
sum( case when month='12' then money else 0 end)十二月
from t_account where year='2012' group by areaid;
-- 按季度统计2012年各个地区的水费
select (select name from t_area where id=areaid) ar,
sum( case when month>='1' and month<=3 then money else 0 end)一季度,
sum( case when month>='4' and month<=6 then money else 0 end)二季度,
sum( case when month>='7' and month<=9 then money else 0 end)三季度,
sum( case when month>='10' and month<=12 then money else 0 end)四季度
from t_account where year='2012' group by areaid;
-- 分析函数
-- rank 相同值排名相同,排名跳跃,对t_account表usenum字段进行排序
select rank() over(order by usenum desc) id,usenum from t_account;
-- rank 相同值排名相同,排名连续,对t_account表usenum字段进行排序
select dense_rank() over(order by usenum desc) id,usenum from t_account;
-- row_number 返回连续的排名,无论值是否相同
select row_number() over(order by usenum desc) id,usenum from t_account;
-- 用row_number()分析函数实现排序
select *
from (select row_number() over(order by usenum desc)rownumber,usenum from t_account)
where rownumber>=10 and rownumber<=20;
