【数据库】oracle常用sql总结(持续更新中)

1、将long型如1350149400000 转换为2014-3-23 13:38:22数据函数(将long型时间转为年月日格式)

    create or replace function num_to_date(in_number NUMBER) return date is
begin
     return(TO_DATE('19700101','yyyymmdd')+ in_number/86400000+
     TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24 );
end num_to_date;

2、删除重复数据

1)、使用distinct函数，重复字段。
        选出所有唯一的数据，sql如下：

    select distinct 字段1,字段2,字段3 from 表名;

       导入临时表：

    create table 临时表名 as select distinct 字段1,字段2,字段3 from 表名; 

       再从临时表恢复。

    insert into 表名 select * from 临时表名;

2)、使用rowid

    delete from 表名 where rowid not in (SELECT min(rowid) FROM 表名 GROUP BY 字段1,字段2,字段3);

       实际业务需求

1)、数据表T(DATE,NUM)，数据如左图所示，要求用一个SQL查出右边结果：

                  

       所用到的关键点，group by，sum，以及union all，SQL

    SELECT T.DATE, SUM(T.NUM) AS NUM FROM T GROUP BY T.DATE
    UNION ALL
    SELECT '合计' AS "DATE", SUM(T.NUM) AS NUM FROM T;

 2）、学生表如下：
         自动编号学号姓名课程编号课程名称分数
         1        2005001  张三  0001      数学    69
         2        2005002  李四  0001      数学    89
         3        2005001  张三  0001      数学    69
         删除除了自动编号不同,其他都相同的学生冗余信息。     

        关键字，group by，rowid，SQL

    delete 学生表 where 自动编号 not in(select min(自动编号) from 
    tablename group by 学号,姓名,课程编号,课程名称,分数);

3）、学生表student如下，列出所有课程分数都大于80的学生姓名

 

       关键字还是group by，SQL如下：

    select name, score from 
    (select name,min(score) score from student group by name)
    where score > 80;

3、按分号截取，判断包含几个字段

    select length('a;b;c;d') - length(replace('a;b;c;d', ';')) dnum from dual t;

       实际业务需求

    select * from 
    (select searchcode, cirelsearchcode, cirelsubtype,
    length(cirelsearchcode) - length(replace(cirelsearchcode, ';')) dnum1, 
    length(cirelsubtype) - length(replace(cirelsubtype, ';')) dnum2 
    from upload_ci_full t)tt
    where tt.dnum1 <> dnum2;

4、to_char与to_date(具体的时间字段和表名都可以修改)

      to_char转换为to_date

   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as curtime from dual; --日期转化为字符串   
   select to_char(sysdate,'yyyy') as curYear from dual;   --获取年   
   select to_char(sysdate,'mm') as curMonth from dual;   --获取月   
   select to_char(sysdate,'dd') as curDay from dual;   --获取日   
   select to_char(sysdate,'hh24') as curHour from dual;  --获取时   
   select to_char(sysdate,'mi') as curMinute from dual;  --获取分   
   select to_char(sysdate,'ss') as curSecond from dual;  --获取秒 

      to_date转为to_char

   select to_date('2014-3-26 11:12:22','yyyy-mm-dd hh24:mi:ss') as curTime from dual;

5、拼接表名，截取字段

   select 'pm_raw_'||substr(ext_datasource,0,1)||'_'||kbp_class from kpi_info;

6、分页rownum

      查询前10条记录

   select * from 表名 where rownum <= 10; -- 使用rownum <> 11 或者 rownum != 11 都可以;

      查询中间记录，注意边界问题。在实际需求中，关注是否需要排序。

   select * from (select rownum rn, 字段名 from 表名) where rn >= 2 and rn <= 10;

      rowid是物理地址，rownum是逻辑地址，有区别。