ITeye博客

Oracle SQL的基本总结

fansofjava · 2011-08-05 17:22 · 数据库 · 数据库 · Markdown

数据库一直是自己的弱项,以前基本上也没涉及到非常复杂的sql,即使有也不是俺做,所以对sql的认识一直很肤浅,在面试的时候也是最怕遇见数据库的问题。 最近常遇见一些相对麻烦一点的sql语句,决定做个记录,以后有机会再来完善。 1.


select m.org_id,(select org_name from org where org_id=m.org_id),count(*) 
from htm_machine m group by m.org_id

上面这句sql的作用是查询htm_machine中,每个org对应的记录条数。这条语句与下面的语句等价:


select o.org_id,o.org_name,ma.m_num from org o
left join (select m.org_id org_id,count(*) m_num from htm_machine m 
group by m.org_id) ma on ma.org_id=o.org_id 
where o.org_id=ma.org_id

2.关于树的递归问题。 员工表staff中含有dep_id字段,dep中的主键为dep_id,还有其父结点parent_id,现要将某部门员工的数量列出来(包括根部门):


SELECT count(*) FROM Staff s where s.dep_id in 
(SELECT dep_id FROM DEP WHERE IS_LEAF = 'Y' AND DEP_TYPE_ID = 2
             AND (status = 'C' or status = 'U')
             START WITH DEP_ID = #depId#
             CONNECT BY PRIOR DEP_ID = PARENT_ID)


上面主要用到的就是start with .. connect by prior,start with表示连接的起始条件, connect by prior表示连接条件。 3.日期处理函数。 由于日期一般会有两种格式:yyyy-mm-dd与yyyymmdd,所以要想将这个字段的值转换成日期,我能想到的就是写个自定义函数。


create or replace function BIRTHFORMAT(bir varchar2)
 return DATE
 is begin
    if bir is null then return bir;
    elsif regexp_like(bir,'[[:digit:]]{4}[-][[:digit:]]{2}[-][[:digit:]]{2}')
    then return to_date(bir,'yyyy-MM-dd');
    elsif regexp_like(bir,'[[:digit:]]{8}') then
    return to_date(bir,'yyyyMMdd');
    else return null;
    end if;
    EXCEPTION 
      when OTHERS then return null;
  end;

上面代码很容易懂,但对于我这个不常写sql的人来说,还是花了不少时间。而且最近发现居然可以用regexp_like来匹配多个要like的值,确实有些意外。

4.一个通过身份证获取年龄的函数:


create or replace function BIRTHDATE(bir in varchar2)
 return varchar2 is
 back varchar2(16);
 begin
       if length(trim(bir))=15 then back:=trunc(months_between(sysdate,to_date('19' || substr(bir, 7, 6),'yyyymmdd'))/12);
       elsif length(trim(bir))=18 then back:=trunc(months_between(sysdate,to_date(substr(bir, 7, 8),'yyyymmdd'))/12);
       end if;
       return back;
EXCEPTION
      when OTHERS then return null;
end;

5.负数作为0处理。如果一个字段为负数,查询出来是把它置为0,因为数据库中没有这么一个方法,所以需要一点技巧,就是通过(a+|a|)/2的方式来解决。 6.日期比较。一般加上一天再用小于,因为默认的比较时间是到00:00:00分。


m.CREATE_TIME<to_date(#endDate#,'yyyy-MM-dd')+1

  1. 从多条记录中随机取出其中的一条。

select * from (select * from user_type order by dbms_random.value) where rownum=1

8.将日期转换成周。如将当前日期转换成当年的第几周:


to_char(sysdate,'iw')

9.处理不是group by列的问题。如果使用了group by,则查询列或者order by列等都只能使用group by里面的列或者采用聚合函数,如果每列的数据都一样,可以直接使用max(列名),这种方法相当有效,减少了很多代码。

评论(0)

暂无评论。

评论功能维护中,当前仅展示历史评论。