1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
|
mni@DIANA> insert into emp values(1000,'TEST', 'TEST', NULL, sysdate, 10875, null, 40)
2 /
1 ligne crÚÚe.
mni@DIANA> select deptno, sum(sal)
2 from emp
3 group by deptno
4 /
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
40 10875
10 8750
mni@DIANA> SELECT MAX(deptno) KEEP(DENSE_RANK FIRST ORDER BY SUM(sal) DESC) AS deptno,
2 MAX(SUM(sal)) AS sum_salary
3 FROM emp
4 GROUP BY deptno
5 /
DEPTNO SUM_SALARY
---------- ----------
40 10875
mni@DIANA> SELECT * FROM
2 (SELECT deptno, SUM(sal)
3 FROM emp
4 GROUP BY deptno
5 ORDER BY 2 DESC
6 ) WHERE rownum = 1
7 /
DEPTNO SUM(SAL)
---------- ----------
20 10875
mni@DIANA> Select deptno, totsal
2 From (
3 select deptno, sum(sal) totsal, rank() over (order by sum(sal) desc) rn
4 from emp
5 group by deptno
6 )
7 Where rn = 1
8 /
DEPTNO TOTSAL
---------- ----------
20 10875
40 10875
mni@DIANA> select deptno, sum(sal) totsal
2 from emp
3 group by deptno
4 having sum(sal) = (Select Max(tot_sal)
5 From
6 (Select Sum(sal) tot_sal
7 from emp
8 group by deptno)
9 )
10 /
DEPTNO TOTSAL
---------- ----------
20 10875
40 10875 |
Partager