常用sql语句

根据一个表的字段更新另一个表字段

 

merger into bmsecubase a 
using bmsecubasesub b
on(a.secu_seq = b.secu_seq)
when matched then
update set a.ps = b.sub_seq

 

分页查询

select decode(exch_id,'HJS','2','3') cal_id,secu_seq
from(select a.* ,rownum rn from (select * from bmsecubase) a where rownum <=201)
where rn >=190

输出2017年所有日期

select to_char(to_date('2017-01-01','yyyy-mm-dd')+rownum -1,'yyyymmdd') as daylist
from dual
connect by rownum <=
trunc(to_date('2017-12-31','yyyy-mm-dd')-to_date('2017-01-01','yyyy-mm-dd'))+1

查询结束日最大值

select secu_seq, max(to_date(payint_end,'yyyy-mm-dd'))
from bmpayintplan where
exists(select max(to_date(payint_end,'yyyy-mm-dd'))
           from bmpayintplan 
             group by secu_seq)
group by secu_seq

 

转载于:https://my.oschina.net/u/3031794/blog/905249