ORACLE 常用

select sysdate from dual;--2020-03-24 23:07:53 2 select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) as nowTime from dual; --日期转化为字符串 2020-03-24 23:08:13 3 select to_char(sysdate,‘yyyy‘) as nowYear from dual; --获取时间的年 2020 4 select to_char(sysdate,‘mm‘) as nowMonth from dual; --获取时间的月 03 5 select to_char(sysdate,‘dd‘) as nowDay from dual; --获取时间的日 24 6 select to_char(sysdate,‘hh24‘) as nowHour from dual; --获取时间的时 23 7 select to_char(sysdate,‘mi‘) as nowMinute from dual; --获取时间的分 07 8 select to_char(sysdate,‘ss‘) as nowSecond from dual; --获取时间的秒 53 9 10 11 select to_date(‘2020-03-24 23:07:53‘,‘yyyy-mm-dd hh24:mi:ss‘) from dual; 12 select to_char( to_date(222,‘J‘),‘Jsp‘) from dual ;--显示Two Hundred Twenty-Two 13 14 15 select to_char(to_date(‘2020-03-24‘,‘yyyy-mm-dd‘),‘dd‘) from dual; -- 24 16 select to_char(to_date(‘2020-03-24‘,‘yyyy-mm-dd‘),‘mm‘) from dual; -- 03 17 select to_char(to_date(‘2020-03-24‘,‘yyyy-mm-dd‘),‘yyyy‘) from dual; --2020 18 ------------------------------------ 19 select sysdate from dual ;--2020/3/24 23:20:46 20 select ‘‘||sysdate||‘‘ from dual ;--24-3月 -20 21 22 23 select to_char(to_date(‘2020-03-24‘,‘yyyy-mm-dd‘),‘dd-mm-yyyy‘,‘NLS_DATE_LANGUAGE = American‘) from dual;--twenty twenty 24 25 SELECT TO_CHAR(sysdate,‘MM/DD/YYYY HH:MI AM‘, ‘NLS_DATE_LANGUAGE = American‘) 26 FROM DUAL;--03/24/2020 11:28 PM 27 28 --求两个日期差 29 select floor(sysdate - to_date(‘20020405‘,‘yyyymmdd‘)) from dual; --6563 30 select floor(sysdate - date‘2002-04-05‘) from dual;--6535 31 32 33 --求月份差 34 select months_between( date‘2010-9-1‘，sysdate) MONTHS FROM DUAL; -- -114.773613724612 35 36 --trunc 37 select trunc(sysdate) from dual;-- 2020/3/24 38 select trunc(sysdate,‘year‘) from dual;-- 2020/1/1 39 select trunc(sysdate,‘month‘) from dual;--2020/3/1 当月第一天 40 select trunc(sysdate,‘day‘) from dual;--2020/3/22 本周第一天 星期天 41 42 select trunc(sysdate,‘month‘)-1 from dual;--2020/2/29 上月最后一天

 

 

select* from emp  ;

 

 

--row_number() 顺序排序 常见去重
select *from (
select t.* , row_number()over (partition by job , deptno order by sal desc) as row_num
from emp t  order by row_num desc )
where row_num =1 ;

 

 

 

 --分析函数

select t.*, sum(sal)over(order by job , sal  ) as job与sal累加,
sum(sal)over() as 总和
 from emp t    ;

 

 

　

 --跳跃排序
 select t.*, rank()over(order by job   ) as 排名
 from emp t    ;
 

 

 

--连续排序
 select t.*, dense_rank()over(order by job   ) as 排名
 from emp t    ;

 

 

 

 

ORACLE 常用

标签：lang   image   where   com   连续   second   排名   ||   rom