Oracle子查询

--子查询


--认识字查询


select min(sal) from emp;


select * from emp where sal=(select min(sal) from emp);


--在where字句中使用子查询


select sal from emp where ename='ALLEN';


select * from emp where sal<(select sal from emp where ename='ALLEN');


select avg(sal) from emp;


select * from emp where sal>(select avg(sal) from emp);


select job from emp where ename='ALLEN';


select sal from emp where empno=7521;


select * from emp where job=(select job from emp where ename='ALLEN')
and sal>(select sal from emp where empno=7521);


select job ,sal from emp where ename='SCOTT';


select * from emp
where (job,sal)=(select job ,sal from emp where ename='SCOTT')
and ename!='SCOTT';


select job,mgr from emp where empno=7566;


select * from emp
where (job,mgr)=(select job,mgr from emp where empno=7566);


select * from emp
where (job,mgr)=(select job,mgr from emp where empno=7566)
and empno!=7566;


select job,to_char(hiredate,'yyyy')
from emp
where ename='ALLEN';


select * from emp
where (job,to_char(hiredate,'yyyy'))=(select job,to_char(hiredate,'yyyy')
from emp
where ename='ALLEN');


select min(sal)from emp group by deptno;


select * from emp
where sal in(select min(sal)from emp group by deptno);


select * from emp
where sal not in(select min(sal)from emp group by deptno);


select mgr from emp;


select e.ename from emp e
where e.empno not in(select mgr from emp);


select min(sal) from emp where job='MANAGER' group by deptno;


select * from emp where sal=any(select min(sal) from emp where job='MANAGER' group by deptno);


select * from emp where sal>any(select min(sal) from emp where job='MANAGER' group by deptno);


select * from emp where sal<>all(select min(sal) from emp where job='MANAGER' group by deptno);


select * from emp where sal>all(select min(sal) from emp where job='MANAGER' group by deptno);


select * from emp where sal<all(select min(sal) from emp where job='MANAGER' group by deptno);


select * from emp where exists(select * from emp where empno=9999);


select * from emp where exists(select * from emp);


select * from emp where not exists(select * from emp where empno=9999);


--在HAVING字句中使用子查询


select avg(sal) from emp;


select deptno,count(empno),avg(sal)
from emp 
group by deptno;


select deptno,count(empno),avg(sal)
from emp 
group by deptno
having avg(sal)>(select avg(sal) from emp);


select max(avg(sal))
from emp
group by deptno;


select d.dname,round(avg(e.sal),2)
from emp e,dept d
where e.deptno= d.deptno
group by d.dname
having avg(sal)=(select max(avg(sal))
from emp
group by deptno);


--在FROM字句中使用子查询


select * from dept;


select deptno dno,count(empno) count,round(avg(sal),2) avg
from emp
group by deptno;


select d.deptno,d.dname,d.loc,temp.count,temp.avg
from dept d,(select deptno dno,count(empno) count,round(avg(sal),2) avg
from emp
group by deptno) temp
where d.deptno=temp.dno(+);


select deptno from dept where dname='SALES';


select empno,ename,sal,comm,job,hiredate
from emp
where deptno=(select deptno from dept where dname='SALES');


select e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,temp.max,temp.min
from emp e,(select deptno dno,max(sal) max,min(sal) min from emp group by deptno) temp
where e.deptno=(select deptno from dept where dname='SALES') and e.deptno=temp.dno;


select avg(sal) from emp;


select e.empno,e.ename,e.sal,e.job,e.hiredate
from emp e
where e.sal>(select avg(sal) from emp);


select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename mname
from emp e,dept d,emp m
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+);


select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename mname,s.grade
from emp e,dept d,emp m,salgrade s
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal;


select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename mname,s.grade, 
temp.count,temp.avg,temp.avgyear
from emp e,dept d,emp m,salgrade s,(
  select deptno dno,count(empno) count,round(avg(sal),2) avg,
  round(avg(months_between(sysdate,hiredate)/12),2) avgyear
  from emp group by deptno) temp
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal
and e.deptno=temp.dno;


select sal from emp where ename in('ALLEN','CLARK');


select e.empno,e.ename,e.sal
from emp e
where e.sal>any(select sal from emp where ename in('ALLEN','CLARK'))
and e.ename not in('ALLEN','CLARK');


select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.sal>any(select sal from emp where ename in('ALLEN','CLARK'))
and e.ename not in('ALLEN','CLARK')
and e.deptno=d.deptno;


select e.empno,e.ename,e.sal,d.dname,m.ename mname
from emp e,dept d,emp m
where e.sal>any(select sal from emp where ename in('ALLEN','CLARK'))
and e.ename not in('ALLEN','CLARK')
and e.deptno=d.deptno
and e.mgr=m.empno(+);


select e.empno,e.ename,e.sal,d.dname,m.ename mname,temp.count
from emp e,dept d,emp m,(
  select deptno dno,count(empno) count from emp group by deptno) temp
where e.sal>any(select sal from emp where ename in('ALLEN','CLARK'))
and e.ename not in('ALLEN','CLARK')
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and d.deptno=temp.dno;


select ename,sal
from emp 
where job='MANAGER';


select e.ename,e.sal,d.dname
from emp e,dept d
where job='MANAGER'
and e.deptno=d.deptno;


select e.ename,e.sal,d.dname,temp.count,temp.avg
from emp e,dept d,(
  select deptno dno,count(empno) count,round(avg(sal),2) avg from emp group by deptno) temp
where job='MANAGER'
and e.deptno=d.deptno
and d.deptno=temp.dno;


--在SELECT字句中使用子查询


select deptno,dname,loc,
  (select count(empno) from emp where deptno=d.deptno) count,
  (select avg(sal) from emp where deptno=d.deptno) avg
from dept d;


--WITH字句


with e as (select * from emp)
select * from e;


with e as(
  select deptno dno,round(avg(sal),2) avg,count(sal) count from emp group by deptno)
  select d.deptno,d.dname,d.loc,e.avg,e.count
from e,dept d
where e.dno(+)=d.deptno;


with e as(
  select deptno dno,max(sal) max
  from emp 
  group by deptno)
select em.empno,em.ename,em.job,em.hiredate,em.sal,d.deptno,d.dname
from e,emp em,dept d
where e.dno=em.deptno and em.sal=e.max and e.dno=d.deptno
order by em.deptno;


--分析函数


select deptno,ename,sal,sum(sal) over(partition by deptno) sum from emp;


select deptno,ename,sal,sum(sal) over() sum from emp;


select sum(sal) from emp;


select deptno,ename,sal,job,sum(sal) over(partition by deptno,job) sum from emp;


select deptno,ename,sal,
  rank() over(partition by deptno order by sal desc) rk
from emp;


select deptno,ename,sal,hiredate,
  rank() over(partition by deptno order by sal,hiredate desc) rk
from emp;


select deptno,ename,sal,hiredate,
  sum(sal)over(order by ename desc) sum
from emp;


select deptno,ename,sal,comm,
  rank() over(order by comm desc nulls last) rk,
  sum(sal) over(order by comm desc nulls last) sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by deptno order by sal range 300 preceding)sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by deptno order by sal range between 0 preceding and 300 following)sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by deptno order by sal range between 0 preceding and current row)sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by deptno order by sal range between unbounded preceding and current row)sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by deptno order by sal rows 2 preceding)sum
from emp;


select deptno,ename,sal,
  sum(sal) over(partition by  deptno order by sal rows between unbounded preceding and unbounded following) sum
from emp;


select *
from (
  select empno,ename,job,sal,deptno,
  count(empno)over(partition by deptno) count,
  round(avg(sal)over(partition by deptno)) avg,
  sum(sal)over(partition by deptno) sum,
  max(sal)over(partition by deptno) max,
  min(sal)over(partition by deptno) min
  from emp) temp
where temp.empno=7369;


select e.empno,e.ename,e.sal,d.dname,d.loc,
  round(avg(sal)over(partition by e.deptno order by sal
        range between unbounded preceding and unbounded following)) avg_salary,
  max(sal)over(partition by e.deptno order by sal
        range between unbounded preceding and unbounded following) max_salary,
  min(sal)over(partition by e.deptno order by sal
        range between unbounded preceding and unbounded following) min_salary
from emp e,dept d
where e.deptno=d.deptno;


select deptno,ename,sal,
  rank() over(partition by deptno order by sal) rank_result,
  dense_rank() over(partition by deptno order by sal) dense_rank_result
from emp;


select deptno,ename,sal,
  row_number() over(partition by deptno order by sal) row_result_deptno,
  row_number() over(order by sal) row_result_all
from emp;


select deptno,
  max(sal) keep(dense_rank first order by sal desc) max_salary,
  min(sal) keep(dense_rank last order by sal desc) min_salary
from emp
group by deptno;


select deptno,empno,ename,sal,
  first_value(sal) over(partition by deptno order by sal
  range between unbounded preceding and unbounded following) first_result,
  last_value(sal) over(partition by deptno order by sal
  range between unbounded preceding and unbounded following) last_result
from emp
where deptno=10;


select deptno,empno,ename,sal,
  lag(sal,2,0) over(partition by deptno order by sal) lag_result,
  lead(sal,2,0) over(partition by deptno order by sal) lead_result
from emp
where deptno=20;


select deptno,ename,sal,
  cume_dist() over(partition by deptno order by sal) cume
from emp
where deptno in(10,20);


select deptno,sal,
  sum(sal) over(partition by deptno order by sal) sum_result,
  ntile(3) over(partition by deptno order by sal) ntile_result_a,
  ntile(6) over(partition by deptno order by sal) ntile_result_b
from emp;


select deptno,sum(sal),
  round(ratio_to_report(sum(sal)) over(),5) rate,
  round(ratio_to_report(sum(sal)) over(),5)*100||'%' precent
from emp
group by deptno;


--行列转换


select deptno,job,sum(sal)
from emp
group by deptno,job;


select deptno,
  sum(decode(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
  sum(decode(job,'MANAGER',sal,0)) MANAGER_JOB,
  sum(decode(job,'ANALYST',sal,0)) ANALYST_JOB,
  sum(decode(job,'CLERK',sal,0)) CLERK_JOB,
  sum(decode(job,'SALESMAN',sal,0)) SALESMAN_JOB
from emp
group by deptno;


select * from (select deptno,job,sal from emp)
pivot(
  sum(sal)
  for job in(
    'PRESIDENT' as president_job,
    'MANAGER' as manager_job,
    'ANALYST' as analyst_job,
    'CLERK' as clerk_job,
    'SALESMAN' as salesman_job
    )
)order by deptno;


select * from (select deptno,job,sal from emp)
pivot xml(
  sum(sal)
  for job in(any)
)order by deptno;


select * from (
  select deptno,job,sal,
  sum(sal) over(partition by deptno) sum_sal,
  max(sal) over(partition by deptno) max_sal,
  min(sal) over(partition by deptno) min_sal
  from emp)
pivot(
  sum(sal)
  for job in(
    'PRESIDENT' as president_job,
    'MANAGER' as manager_job,
    'ANALYST' as analyst_job,
    'CLERK' as clerk_job,
    'SALESMAN' as salesman_job
    )
)order by deptno;


select * from (select deptno,job,sal from emp)
pivot(
  sum(sal) as sum_sal,max(sal) as sum_max
  for job in(
    'PRESIDENT' as president_job,
    'MANAGER' as manager_job,
    'ANALYST' as analyst_job,
    'CLERK' as clerk_job,
    'SALESMAN' as salesman_job
    )
)order by deptno;


select * from(select deptno,job,sal,sex from emp)
pivot(
  sum(sal) as sum_sal,max(sal) as sum_max
  for(job,sex) in(
    ('MANAGER','男') as manager_male_JOB,
    ('manager','女') as manager_female_JOB,
    ('CLERK','男') as clerk_male_JOB,
    ('CLERK','女') as clerk_female_JOB
  )
)order by deptno;


with temp as(
  select * from (select deptno,job,sal from emp)
pivot(
  sum(sal)
  for job in(
    'PRESIDENT' as president_job,
    'MANAGER' as manager_job,
    'ANALYST' as analyst_job,
    'CLERK' as clerk_job,
    'SALESMAN' as salesman_job
    )
)order by deptno)
select * from temp
unpivot(
  sal_sum for job in(
    president_job as 'PRESIDENT',
    manager_job as 'MANAGER',
    analyst_job as 'ANALYST',
    clerk_job as 'CLERK',
    salesman_job as 'SALESMAN'
  )
)order by deptno;


with temp as(
  select * from (select deptno,job,sal from emp)
pivot(
  sum(sal)
  for job in(
    'PRESIDENT' as president_job,
    'MANAGER' as manager_job,
    'ANALYST' as analyst_job,
    'CLERK' as clerk_job,
    'SALESMAN' as salesman_job
    )
)order by deptno)
select * from temp
unpivot include nulls(
  sal_sum for job in(
    president_job as 'PRESIDENT',
    manager_job as 'MANAGER',
    analyst_job as 'ANALYST',
    clerk_job as 'CLERK',
    salesman_job as 'SALESMAN'
  )
)order by deptno;


--设置数据层次


select empno,lpad('|-',level*2,' ')||ename empname,mgr,level
from emp
connect by prior empno=mgr
start with mgr is null;


select empno,lpad('|-',level*2,' ')||ename empname,mgr,level,
decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf
from emp
connect by prior empno=mgr
start with mgr is null;


select empno,lpad('|-',level*2,' ')||ename empname,mgr,level,
connect_by_root ename
from emp connect by prior empno=mgr
start with empno=7566;


select empno,lpad('|-',level*2,' ')||sys_connect_by_path(ename,' => ')
empname,mgr,level,
  decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf
from emp
connect by prior empno=mgr
start with mgr is null;


select empno,lpad('|-',level*2,' ')||sys_connect_by_path(ename,' => ')
empname,mgr,level,
  decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf
from emp
connect by prior empno=mgr and empno!=7698
start with mgr is null;


select ename,lpad('|-',level*2,' ')||ename empname,level,
  decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf
from emp
connect by prior empno=mgr
start with mgr is null
order by ename;


select ename,lpad('|-',level*2,' ')||ename empname,level,
  decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf
from emp
connect by prior empno=mgr
start with mgr is null
order siblings by ename;


select ename,lpad('|-',level*2,' ')||ename empname,level,
  decode(connect_by_isleaf,0,'根节点',1,'  叶子节点') isleaf,
  decode(connect_by_iscycle,0,'【yes】没有循环',1,'【no】存在循环') iscycle
from emp
connect by nocycle prior empno=mgr
start with empno=7839
order siblings by ename;