oracle sql

1. rownum

oracle前10条
select * from accounts t where rownum<11

 

分页 取 10 到20条 :
select * from (select t.*,rownum r from accounts t) a  where  a.r>=10   and   a.r<=20;

 

分页:

select * from (select row_.*,rownum rownum_ from (真正的SQL语句) row_ where rownum <=?) where rownum_ > ?

 

2. 随机取5条   order by dbms_random.value() 
select * from (select t.id,t.user_type from accounts t order by dbms_random.value()) x where rownum <= 5

 

3. nulls sort

3.1 first: 空值在最前
select ename,sal,comm from emp order by comm nulls first

 

3.2 last: 空值在最后
select ename,sal,comm from emp order by comm nulls last

 

4. to_date:
SELECT TO_DATE(t.recent_access_date, 'YYYY-MM-DD HH24:MI:SS') as expiredate from accounts t

SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as nowTime from dual

 

5. 求时间差:  sysdate 是今天的时间
round(to_number(end-date-start_date))- 消逝的时间（以天为单位）
round(to_number(end-date-start_date)*24)- 消逝的时间（以小时为单位）
round(to_number(end-date-start_date)*1440)- 消逝的时间（以分钟为单位）

 

select round(to_number(to_date('2006-11-30','YYYY-MM-DD')-to_date('2006-10-01','YYYY-MM-DD'))) from dual;

select round(sysdate-to_date('2006-10-01','YYYY-MM-DD')) from dual;

 

6. coalesce(t.src_type,'0') 把空的用0代替

select t.user_name,t.user_type, coalesce(t.src_type,'0') from accounts t

 

7. ADD_MONTHS

select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

 

select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

 

8. substr

select substr('1234567890', 9, 1) value from dual

 

9..替换函数
select * from accounts  acc where replace(acc.pas,'-','') like '%057185663557%'

 

10. 获取上个月第1天

上个月第1天: 这个月最后一天+1天(也就是下个月1号)，然后再减去2个月
select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;

 

上个月最后1天

select to_char(add_months(last_day(sysdate),-1),'yyyy-mm-dd') LastDay from dual

 

11.

DDD当年第几天
select to_char(sysdate,'DDD') from dual;

 

DD当月第几天
select to_char(sysdate,'DD') from dual;

 

D这周第几天
select to_char(sysdate,'D') from dual;