常用sql语句

1.oracle中的时间转换函数

1.将日期转换成字符串
select to_char(sysdate,'dd-mm-yyyy day') from dual;

2.将字符串转换成日期格式
select to_date('2012-05-03','yyyy-mm-dd') from dual ;

3.保留2位小数trunc(float,count)
select trunc(124.16666,2) from dual;

4.截取字符串，substr(str,start,count)
select substr('138888888',1,6) from dual;

5.增加月份或者减去月份 add_months(sysdate,1)
select to_char(add_months(to_date('2012-5-03','yyyy-mm-dd'),1),'yyyy-mm-dd') from dual;

6.返回日期的最后一天last_day
select last_day(sysdate) from dual;


7.oracle中的递归查询

8.递归查询
select d.* from test_tree d start with d.pid=0 connect by prior d.id=d.pid
SELECT column   FROM table_name    START WITH column=value    CONNECT BY PRIOR 父主键=子外键  

select d.* from (select dd.* from test_tree dd order by dd.ind) d start with d.pid=0 connect by prior d.id=d.pid

9.去除重复的高效sql语句
delete from test_tree t1 where t1.rowid>(select min(t2.rowid) from test_tree t2 where t1.id=t2.id);

10.查询重复的数据
select * from test_tree t1 where t1.rowid !=(
select max(t2.rowid) from test_tree t2 where t1.id=t2.id
)


11.使用distinct去除重复

select distinct id from test_tree


12.分页sql语句
select * from (

select t1.*, rownum rn from (

select * from test_tree ) t1 where rownum<= 4
)
where rn >=1 and rownum<=4


13.左连接
select A.c1,B.c2 from A left join B on A.c3 = B.c3;