数据库一直是自己的弱项，以前基本上也没涉及到非常复杂的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 oleft 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(列名)，这种方法相当有效，减少了很多代码。