Oracle SQL的基本总结

数据库一直是自己的弱项，以前基本上也没涉及到非常复杂的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

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

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(列名)，这种方法相当有效，减少了很多代码。