uj5u.com熱心網友回復：
select * from (select * from t order by salary desc
)
where rownum <= 3
uj5u.com熱心網友回復：
select * from twhere salary > (select avg(salary) from t)
uj5u.com熱心網友回復：
selectsum(case when yyyy = 2015 then 1 else 0 end) C2015,
…
…
from t
uj5u.com熱心網友回復：
--找到員工工資最高的前三名 使用分頁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')
uj5u.com熱心網友回復：
--1select 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);
uj5u.com熱心網友回復：
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);
uj5u.com熱心網友回復：
使用分析函式，例外強大印象中從oracle9或oarcle10開始支持
百度下，你會發現新大陸
uj5u.com熱心網友回復：
--找到員工工資最高的前三名 使用分頁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')
uj5u.com熱心網友回復：
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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/71761.html
標籤：開發
上一篇：oracle 字符集 ZHS16GBK 與utf8 的問題
下一篇：請問有知道sqlldr的用法嗎？