其它sql

1.求某一年的天数

  select 'Days in 2010: '|| to_char(add_months(trunc(sysdate,'y'),12)-1,'DDD') from dual

 

2.查找字母数字混合的字符串

 with temp as(
select '123434' id from dual
union all
select '2323 aa' id from dual
union all
select 'aaaadgb' id from dual
union all
select 'aa 123' id from dual
)
select id from(
select id,translate(id,'abcdefghijklmnopqrstuvwxyz0123456789',rpad('#',26,'#')||rpad('*',10,'*')) idx from temp
) where instr(idx,'*') > 0 and instr(idx,'#') > 0

 

3.转置已分等级的结果集

 select max(decode(grp,1,ename||'('||sal||')',null)),
max(decode(grp,2,ename||'('||sal||')',null)),
max(decode(grp,3,ename||'('||sal||')',null)) from(
select ename,sal,rnk,case when rnk <= 3 then 1 when rnk <=6 then 2 else 3 end grp,
row_number() over(partition by case when rnk <= 3 then 1 when rnk <= 6 then 2 else 3 end order by sal desc,ename) grp_rnk
 from(
select ename,sal,dense_rank()over(order by sal desc) rnk from emp
)
) group by grp_rnk

 

4.给两次转置的结果集增加列头

 with it_research as(
select 100 deptno,'A' ename from dual
union all
select 100 deptno,'B' ename from dual
union all
select 100 deptno,'C' ename from dual
union all
select 200 deptno,'D' ename from dual
union all
select 200 deptno,'P.E' ename from dual
union all
select 200 deptno,'F' ename from dual
union all
select 300 deptno,'G' ename from dual
union all
select 300 deptno,'H' ename from dual
union all
select 300 deptno,'J.I' ename from dual
),it_apps as(
select 400  deptno,'J' ename from dual
union all
select 400  deptno,'K' ename from dual
union all
select 400  deptno,'L' ename from dual
union all
select 400  deptno,'M' ename from dual
union all
select 400  deptno,'N' ename from dual
union all
select 500  deptno,'O' ename from dual
union all
select 500  deptno,'P' ename from dual
union all
select 600  deptno,'Q' ename from dual
union all
select 600  deptno,'R' ename from dual
union all
select 600  deptno,'S' ename from dual
union all
select 600  deptno,'T' ename from dual
union all
select 700  deptno,'U' ename from dual
union all
select 700  deptno,'V' ename from dual
union all
select 700  deptno,'W' ename from dual
)
select max(decode(flag2,0,it_dept)) research,max(decode(flag2,0,it_dept)) apps from(
 select sum(flag1) over(partition by flag2 order by flag1,rownum) flag,it_dept,flag2 from(
  select 1 flag1,0 flag2,decode(rn,1,to_char(deptno),'   '||ename) it_dept from(
   select x.*,y.id,row_number() over(partition by x.deptno order by y.id) rn from(
    select deptno,ename,count(*) over(partition by deptno) cnt from it_research) x,
    (select level id from dual connect by level <= 2)  y
    )where rn <= cnt + 1
    union all
    select 1 flag1,1 flag2,decode(rn,1,to_char(deptno),'   '||ename) it_dept from(
      select x.*,y.id,row_number() over(partition by x.deptno order by y.id) rn from(
       select deptno,ename,count(*) over(partition by deptno) cnt from it_apps) x,
       (select level id from dual connect by level <= 2) y)
     where rn <=cnt+1) tmp1) tmp2 group by flag 

 

5.把标量子查询变更为复合子查询

create type generic_obj as object(
 val1 varchar2(10),
 val2 varchar2(10),
 val3 date
)

select x.deptno,x.ename,x.multival.val1 dname,
       x.multival.val2 loc,
       x.multival.val3 today
       from(
        select e.deptno,e.ename,e.sal,
        (select generic_obj(d.dname,d.loc,sysdate+1) from dept d
         where e.deptno = d.deptno) multival
         from emp e) x order by x.deptno

 

6.把连续数据分解为行

 with temp as(
select level kk from dual connect by level <= 100
),temp as(
select 'entry:abc:dc:fa:' id from dual
union all
select 'entry:as::adsf:' from dual
union all
select 'entry:::lkjl:' from dual
union all
select 'entry:while:' from dual
)
select max(decode(kk,1,substr(id,p1+1,p2-1))) val1,
       max(decode(kk,2,substr(id,p1+1,p2-1))) val2,
       max(decode(kk,3,substr(id,p1+1,p2-1))) val3
from(      
select id,kk,instr(id,':',1,kk) p1,
    instr(id,':',1,kk+1) - instr(id,':',1,kk) p2
from temp,tt
where kk < (length(id)-length(replace(id,':')))+1   
) group by id order by 1

 

7.计算相对于总数的百分数

select job,count(cnt),ceil(max((dept_sal/total)*100)) from(
select job,count(empno) cnt,sum(sal) over(partition by job order by job) dept_sal,
sum(sal) over() total from emp group by job,sal
) group by job

 

8.创建目录

CREATE OR REPLACE DIRECTORY IMAGES AS 'C:/picture';

 

9.求剩余金额

with CGPO as(
select to_date('2010-09-4','yyyy-mm-dd') sj,600 cgsl from dual
),WLXQMX as (
select to_date('2010-09-1','yyyy-mm-dd') sj,300 xqsl from dual
union all
select to_date('2010-09-2','yyyy-mm-dd') sj,260 xqsl from dual
union all
select to_date('2010-09-6','yyyy-mm-dd') sj,780 xqsl from dual
)
select sj,xqsl,cgsl,700+sum(nvl(xqsl,0)*flag+nvl(cgsl,0)*flag) over(order by sj)
from
(
select sj,null xqsl,cgsl,1 flag from cgpo
union all
select sj,xqsl,null cgsl,-1 flag from wlxqmx
) order by sj,flag desc

 

10.去除指定值

SQL> select * from tb1;

QSHM  ZZHM ZT
----- ----- ----------
1001  1010 zc
1003  1004 zf
1007  1007 zf

SQL>
SQL> with t1 as (select rownum-1 rn from dual connect by rownum<=(select max(zzhm-qshm) from tb1)),
  2  t2 as (select qshm+rn qshm ,decode(zt,'zc',0,1) zt from tb1,t1 where tb1.qshm+t1.rn<=tb1.zzhm),
  3  t3 as (select qshm,sum(zt) zt from t2 group by qshm having sum(zt)=0),
  4  t4 as (select qshm,qshm+row_number()over(order by qshm desc) rn from t3)
  5  select min(qshm),max(qshm) from t4 group by rn;
MIN(QSHM)  MAX(QSHM)
---------- ----------
      1001       1002
      1005       1006
      1008       1010

SQL>

 

10.求时间段内每天工作人数

 

如有一个表
工号 拉号 入厂日期 离职日期
01 A1 2010/10/01 2010/10/10
02 A1 2010/10/01 2010/10/05
03 A2 2010/10/02 2010/10/05
有一个WEB页面，输入日期范围2010/10/02—2010/10/06
怎样用SQL语句显示以下资料
日期 拉号 员工总数
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0

 

> select * from tbl_cal;

WORKNUM    GROUPNUM   HIREDATE    FIREDATE
---------- ---------- ----------- -----------
01         A1         2010-10-1   2010-10-10
02         A1         2010-10-1   2010-10-5
03         A2         2010-10-2   2010-10-5

SQL>
SQL> select b.caldate, b.groupnum, count(a.groupnum)
  2    from tbl_cal a,
  3         (select distinct caldate, groupnum
  4            from tbl_cal,
  5                 (SELECT to_date('2010/10/02', 'yyyy/mm/dd') + rownum - 1 caldate
  6                    from dual
  7                  connect by rownum <= (to_date('2010/10/06', 'yyyy/mm/dd') -
  8                             to_date('2010/10/02', 'yyyy/mm/dd')))) b
  9   where b.caldate between a.hiredate(+) and a.firedate(+)
10     and b.groupnum = a.groupnum(+)
11   group by b.caldate, b.groupnum
12  ;

CALDATE     GROUPNUM   COUNT(A.GROUPNUM)
----------- ---------- -----------------
2010-10-2   A1                         2
2010-10-2   A2                         1
2010-10-3   A1                         2
2010-10-3   A2                         1
2010-10-4   A1                         2
2010-10-4   A2                         1
2010-10-5   A1                         2
2010-10-5   A2                         1
2010-10-6   A1                         1
2010-10-6   A2                         0

10 rows selected

10.整数后保留2位小数

select to_char(round('123.00',2),'fm999,990.00') from dual;

11.分组加空行

with temp as(
select 22 a,11 b from dual
union all
select 22 a,31 b from dual
union all
select 22 a,21 b from dual
union all
select 34 a,22 b from dual
union all
select 23 a,32 b from dual
union all
select 23 a,23 b from dual
union all
select 23 a,12 b from dual
union all
select 34 a,13 b from dual
)
select case when b is null then null else a end a,b from(
select a,b,max(a) c from(
select a, b  from temp
union all
select a,null b from temp group by a
) group by a,b
) order by c,a

11.求单位时间内人数

A表中有开始时间 sttime,结束时间 edtime 等字段， 要求是，求用户接听时长的时间段：小于1分钟，1分钟到多少分钟（其中的多少分钟是用户自己选定的时间间隔），大于一个小时的，比如输入15分钟， 通话时长：小于1分钟的有多少个用户， 1分到15分的有多少个用户 15分到30分的有多少个用户 30分到45分的有多少个用户 45分到60分的有多少个用户大于1小时的有多少个用户 要是输入3分钟，时间间隔就是3 小于1分钟的有多少个用户， 1分到3分的有多少个用户 3分到6分的有多少个用户 ........... 输入的数大于1分小于60分 这样的sql 语句怎么写

with temp as (

 select to_date('2011-01-27 20:20','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:30','yyyy-mm-dd hh24:mi')as edtime from dual

 union all

select to_date('2011-01-27 20:13','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:59','yyyy-mm-dd hh24:mi')as edtime from dual )

,temp1 as(

 select level lv from dual connect by level < 61

)

select 1 ms, sum(case when ((edtime-sttime)*24*60) <= 15 then 1 else 0 end) splittime from temp

 union all

select 15*lv ms,sum(case when ((edtime-sttime)*24*60) >15*lv and (edtime-sttime)*24*60 <=15*(lv+1) then 1 else 0 end) from temp,temp1 where 15*lv <60 group by 15*lv

 union all

 select 60 ms, sum(case when ((edtime-sttime)*24*60) > 60 then 1 else 0 end) splittime from temp order by ms