oracle笔记

修改日期为格式字符串

to_char(date, 'yyyy/mm')

获取当前日期往前6个月

add_month(sysdate, -5)

 

order by 要放在group by 后面

SELECT FORMATDATE,
       DEALER,
       SUM(AMOUNT)
  FROM SHIPMENTS
 WHERE FORMATDATE >= TO_CHAR(ADD_MONTHS(SYSDATE - 1, -5), 'yy.mm')
   AND FORMATDATE <= TO_CHAR(SYSDATE - 1, 'yy.mm')
 GROUP BY FORMATDATE,
          DEALER
 ORDER BY FORMATDATE;

 

除数不为0

在oracle除数不能为零  好吧，出了问题就得想法解决

例  

select a/b as w where 表

解

decode(b, 0, null, a/b) 

一般来说，我会在除法算式前加decode

这样 如果b为0，则输出null ，不为零则输出a/b

 

Rank排名

select e.ename, e.sal, rank() over(order by e.sal desc) "Rank" from emp e;

 

 

substr

SHOW SUBSTR('abcdefg',3,4) 
cdef

 

 

选取前10条记录

SELECT * FROM TBNAME WHERE ROWNUM<11;

select * from sometable
order by name
limit 20

 

 

按字符分割字符串

SUBSTR(T.MODEL, INSTR(T.MODEL, '-', 1, 1) + 1, 10)