草稿，随笔(慌乱的记忆)

1、SQL> SELECT T1.C1,T1.C2,T2.C2 FROM ((SELECT 'A' C1,'1' C2 FROM dual
  2                                  UNION ALL
  3                                  SELECT 'B' C1,'2' C2 FROM dual) T1
  4                                  left join
  5                                 (SELECT 'B' C1,'3' C2 FROM dual
  6                                  UNION ALL
  7                                  SELECT 'D' C1,'5' C2 FROM dual) T2
  8                                  ON T1.C1=T2.C1)
  9  UNION
 10  SELECT T2.C1,T1.C2,T2.C2 FROM ((SELECT 'A' C1,'1' C2 FROM dual
 11                                  UNION ALL
 12                                  SELECT 'B' C1,'2' C2 FROM dual) T1
 13                                  right join
 14                                 (SELECT 'B' C1,'3' C2 FROM dual
 15                                  UNION ALL
 16                                  SELECT 'D' C1,'5' C2 FROM dual) T2
 17                                  ON T1.C1=T2.C1)
 18  ;
 
C1 C2 C2
--    --    --
A    1 
B    2    3
D          5

 

2、SQL> SELECT * FROM lsb_a;
 
 NO NAME
--- -----
  1 a
  2 b
  3 c
  4 d

 

SQL> SELECT REPLACE(WM_CONCAT(dd),',',' ')
  2         FROM (SELECT name||cc AS dd FROM (SELECT s.name,b.name AS cc FROM (SELECT name,rownum rn FROM lsb_a) s,
  3                                                                                                                               (SELECT name,rownum rn FROM lsb_a) b
  4                                                                                                                     WHERE s.rn<b.rn));
 
REPLACE(WM_CONCAT(DD),',','')
--------------------------------------------------------------------------------
ab ac ad bc bd cd

3、给出两个时间，统计休假小时，工作时间有效
with w1 as (
select
   substr('20121108140000',9,2) e  --qian
 ,substr('20121110120000',9,2) d  --hou
 ,(to_date(substr('20121110120000',1,8),'yyyymmdd')
 -to_date(substr('20121108140000',1,8),'yyyymmdd')-1) c
 from dual )
 select case when e between 09 and 12 then e - 9
        else 18- e end +
        case when d between 09 and 12 then d - 9
        else 18- d end + w1.c*8  as time       
 from w1 

得益于朋友指点，跟自己语句一对比，果然角度很重要。
但以上语句并不够健壮，但还是有很大启发，修改如下：
with w1 as (select substr(date1,1,8) d1,
                  substr(date2,1,8) d2,
                  substr(date1,9,2) h1,
                  substr(date2,9,2) h2,
                  substr(date1,11,2) m1,
                  substr(date2,11,2) m2
                  from (select '20121110102000' date1,'20121111084000' date2 from dual))
select round(((d2-d1)*8+case when h2 between 09 and 11 then (h2-9)+m2/60
                      when h2 = 12 then 4
                      when h2 between 13 and 18 then (h2-10)+m2/60
                      when h2>18 then 8
                      else 0 end
                -case when h1 between 09 and 11 then (h1-9)+m1/60
                      when h1 = 12 then 4
                      when h1 between 13 and 18 then (h1-10)+m1/60
                      when h1>18 then 8
                      else 0 end),1) hours
        from w1
增加对周六日的忽略：
with w1 as (select substr(date1,1,8) d1,
                  substr(date2,1,8) d2,
                  substr(date1,9,2) h1,
                  substr(date2,9,2) h2,
                  substr(date1,11,2) m1,
                  substr(date2,11,2) m2
                  from (select '201211230830' date1,'201211261230' date2 from dual)),
   w2 as (select to_date(d1,'yyyymmdd') date1,d1,to_date(d2,'yyyymmdd') date2,d2,
                 round((case when h2=08 and m2>=30 then (m2-30)/60
                      when h2 between 09 and 11 then (h2-8.5)+m2/60
                      when h2=12 then 4
                      when h2=13 and m2>=30 then (h2-9.5)+(m2-30)/60
                      when h2 between 14 and 16 then (h2-9.5)+m2/60
                      when h2=17 and m2<=30 then (h2-9.5)+m2/60
                      when h2=17 and m2>30 then 8
                      when h2>18 then 8
                      else 0 end
                -case when h1=08 and m1>=30 then (m1-30)/60
                      when h1 between 09 and 11 then (h1-8.5)+m1/60
                      when h1=12 then 4
                      when h1=13 and m1>=30 then (h1-9.5)+(m1-30)/60 
                      when h1 between 14 and 16 then (h1-9.5)+m1/60
                      when h1=17 and m1<=30 then (h1-9.5)+m1/60
                      when h1=17 and m1>30 then 8 
                      when h1>18 then 8
                      else 0 end),1) hours
        from w1)
select sum_d+hours from(select sum(date3) sum_d from(select decode(to_char(date1+rownum-1,'D'),7,0,1,0,1)*8 date3 from w2
                                                            connect by rownum<= date2-date1)) w3,w2


4、根据整体分类，colc为日增，cold为累积增长，根据分类求和，sum(cold)为当天max(nvl(cold,0))over(partition by cola,colb order by date1)的总和
with w1 as (select '1' date1,'A' cola,'a1' colb,10 colc,1 cold from dual
            union all
            select '1' date1,'A' cola,'a2' colb,11 colc,2 cold from dual
            union all
            select '1' date1,'B' cola,'b1' colb,13 colc,1 cold from dual
            union all
            select '2' date1,'A' cola,'a1' colb,5 colc,2 cold from dual
            union all
            select '3' date1,'A' cola,'a2' colb,7 colc,3 cold from dual
            union all
            select '3' date1,'C' cola,'c1' colb,9 colc,4 cold from dual)
select date1,
             sum(colc) sum_c,
             sum(cold) sum_d
from
(select a1.date1,
              a1.cola,
              a1.colb,
              nvl(b1.colc, 0) colc,
              max(nvl(b1.cold, 0)) over(partition by a1.cola, a1.colb order by a1.date1) cold
   from (select b.date1,
                            a.cola,
                            a.colb
                 from (select cola, colb from w1 group by cola, colb) a,
                            (select date1 from w1 group by date1) b
               ) a1,
              w1 b1
where a1.date1 = b1.date1(+)
     and a1.cola = b1.cola(+)
     and a1.colb = b1.colb(+)
)
group by date1;

DATE1    COLA     COLB      COLC      COLD
-----        ----       ----        ----------    ----------
1            A        a1           10            1
1            A        a2           11            2
1            B        b1           13            1

2            A        a1            5             2

3            A        a2            7             3
3            C        c1            9             4
期望效果：
date1   sum_c    sum_d
1     10+11+13   1+2+1
2         5           2+2+1
3        7+9        2+3+1+4
实际效果：
DATE1      SUM_C      SUM_D
----- ---------- ----------
1             34          4
2              5          5
3             16         10   

partition outer join为另一朋友指点，留待以后研究。

5、提取X1=2，X3=4，X5=6    等号后面的值

SELECT regexp_substr('&1','[^=]+,',1,level) a FROM dual
CONNECT BY level < 8

（群友提供，留待学习）