Oracle Group By Order By

秒速五厘米 2022-07-12 13:45 185阅读 0赞

1.GROUP BY  
**主要用来对一组数进行统计**  
SQL> select deptno,count(\*),sum(sal) from scott.emp group by deptno;

DEPTNO  COUNT(\*)  SUM(SAL)  
\--------- --------- ---------  
       10         3      8750  
       20         5     10875  
       30         6      9400

2.HAVING  
**对分组统计再加限制条件**  
SQL> select deptno,count(\*),sum(sal) from scott.emp group by deptno having count(\*)>=5;

DEPTNO  COUNT(\*)  SUM(SAL)  
\--------- --------- ---------  
       20         5     10875  
       30         6      9400  
SQL> select deptno,count(\*),sum(sal) from scott.emp having count(\*)>=5 group by deptno ;

DEPTNO  COUNT(\*)  SUM(SAL)  
\--------- --------- ---------  
       20         5     10875  
       30         6      9400

67.ORDER BY  
**用于对查询到的结 果进行排序输出**  
 SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

DEPTNO ENAME            SAL  
\--------- ---------- ---------  
       10 KING            5000  
       10 CLARK           2450  
       10 MILLER          1300  
       20 SCOTT           3000  
       20 FORD            3000  
       20 JONES           2975  
       20 ADAMS           1100  
       20 SMITH            800  
       30 BLAKE           2850  
       30 ALLEN           1600  
       30 TURNER          1500  
       30 WARD            1250  
       30 MARTIN          1250  
       30 JAMES            950