CodePudding user response:

Select * from (
Select * from t order by salary desc
)
Where rownum & lt;=3

CodePudding user response:

Select * from t
Where salary & gt; (select avg (salary) from t)

CodePudding user response:

Select
The sum (case when yyyy=2015 then 1 else 0 end) C2015,

...
...

from t

CodePudding user response:

The top three - to find the highest wages using paging
Select t2. * from (select t. *, rownum rn from (select e. * from emp2 e order by e.s al desc) t) t2 where t2. Rn<=3;
- find the staff salary is more than the average salary of your department staff
Select e2. *, t.a from emp2 e2, (select e.d eptno, avg (e.s al) from a emp2 e group by e.d eptno) t
Where e2. Deptno=t.d eptno
And e2. Sal> T.a
- statistics every year into the job number
The select to_char (e.h iredate, 'yyyy), count (e.e mpno) from emp2 e group by to_char (e.h iredate,' yyyy)

CodePudding user response:

1
-Select rownum, a.e mpno, a.e name, a.s al the from (select * from Scott. The emp order by sal desc) where a rownum> 0 and rownum<4.
2
-Select * from Scott. Emp e where e.s al> (select avg (sal) avgsal from Scott. The emp where e.d eptno=deptno);
3
-Select count (1), extract (year from hiredate) from Scott. The emp e group by extract (year from hiredate);

CodePudding user response:

Select rownum, a.e mpno, a.e name, a.s al the from (select * from Scott. The emp order by sal desc) where a rownum> 0 and rownum<4.
Select * from Scott. Emp e where e.s al> (select avg (sal) avgsal from Scott. The emp where e.d eptno=deptno);
Select count (1), extract (year from hiredate) from Scott. The emp e group by extract (year from hiredate);

CodePudding user response:

Using analysis function, so powerful
Impression since oracle9 or oarcle10 support
Baidu, you will find the new world

CodePudding user response:

The top three - to find the highest wages using paging
Select t2. * from (select t. *, rownum rn from (select e. * from emp2 e order by e.s al desc) t) t2 where t2. Rn<=3;
- find the staff salary is more than the average salary of your department staff
Select e2. *, t.a from emp2 e2, (select e.d eptno, avg (e.s al) from a emp2 e group by e.d eptno) t
Where e2. Deptno=t.d eptno
And e2. Sal> T.a
- statistics every year into the job number
The select to_char (e.h iredate, 'yyyy), count (e.e mpno) from emp2 e group by to_char (e.h iredate,' yyyy)

CodePudding user response:

 
Select * from (select salary, rownum rn from (select * from emp order BY salary desc)) where rn<=3; 

Select * from emp, (the select job_id, avg (min_salary) from emp group by empno) 
Where salary & gt; (select avg (salary) from emp);