select * from t where salary > (select avg(salary) from t)
select sum(case when yyyy = 2015 then 1 else 0 end) C2015,… …from t
--找到员工工资最高的前三名 使用分页 select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3; --找到员工薪水大于本部门平均薪水的员工 select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t where e2.deptno=t.deptno and e2.sal>t.a --统计每年入职人数 select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')
--1 select rownum,a.empno,a.ename,a.sal from (select * from scott.emp order by sal desc) a where rownum>0 and rownum<4; --2 select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno); --3 select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);
select rownum,a.empno,a.ename,a.sal from (select * from scott.emp order by sal desc) a where rownum>0 and rownum<4; select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno); select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);
使用分析函数,异常强大 印象中从oracle9或oarcle10开始支持 百度下,你会发现新大陆
--找到员工工资最高的前三名 使用分页 select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3; --找到员工薪水大于本部门平均薪水的员工 select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t where e2.deptno=t.deptno and e2.sal>t.a --统计每年入职人数 select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')
select * from (select salary,rownum rn from (select * from emp order BY salary desc) ) where rn<=3;select * from emp,(select job_id,avg(min_salary)from emp group by empno) where salary > (select avg(salary) from emp);
where salary > (select avg(salary) from t)
sum(case when yyyy = 2015 then 1 else 0 end) C2015,…
…from t
select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3;
--找到员工薪水大于本部门平均薪水的员工
select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t
where e2.deptno=t.deptno
and e2.sal>t.a
--统计每年入职人数
select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')
select rownum,a.empno,a.ename,a.sal from (select * from scott.emp order by sal desc) a where rownum>0 and rownum<4;
--2
select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno);
--3
select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);
select * from scott.emp e where e.sal>(select avg(sal) avgsal from scott.emp where e.deptno=deptno);
select count(1),extract(year from hiredate) from scott.emp e group by extract(year from hiredate);
印象中从oracle9或oarcle10开始支持
百度下,你会发现新大陆
select t2.* from (select t.*,rownum rn from (select e.* from emp2 e order by e.sal desc) t) t2 where t2.rn<=3;
--找到员工薪水大于本部门平均薪水的员工
select e2.*,t.a from emp2 e2,(select e.deptno,avg(e.sal) a from emp2 e group by e.deptno) t
where e2.deptno=t.deptno
and e2.sal>t.a
--统计每年入职人数
select to_char(e.hiredate,'yyyy'),count(e.empno) from emp2 e group by to_char(e.hiredate,'yyyy')
select * from (select salary,rownum rn from (select * from emp order BY salary desc) ) where rn<=3;select * from emp,(select job_id,avg(min_salary)from emp group by empno)
where salary > (select avg(salary) from emp);