曾经很长时间不会写的两个SQL语句(group by，having)

谁践踏了优雅 2022-03-31 11:08 56阅读 0赞

1、统计各部门的平均工资，平均资金（要求同时显示出部门名称，部门编号，部门总人数）

SQL> SELECT DNAME 部门名称,D.DEPTNO 部门编号,COUNT(ENAME) 部门总人数,ROUND(AVG(NVL(SAL,0)),2) 部门平均工资,ROUND(AVG(NVL(COMM,0)),1) 部门平均资金 
         FROM EMP E RIGHT JOIN DEPT D 
         ON E.DEPTNO=D.DEPTNO 
         GROUP BY DNAME,D.DEPTNO
    ORDER BY D.DEPTNO;
    
    部门名称         部门编号    部门总人数  部门平均工资    部门平均资金
    -------------- ---------- ---------- ------------ ------------
    ACCOUNTING             10          3      2916.67            0
    RESEARCH               20          5         2175            0
    SALES                  30          6      1566.67        366.7
    OPERATIONS             40          0            0            0

2、同上，只显示部门编号为10，20，30的信息

SQL> SELECT DNAME 部门名称,D.DEPTNO 部门编号,COUNT(ENAME) 部门总人数,ROUND(AVG(NVL(SAL,0)),2) 部门平均工资,ROUND(AVG(NVL(COMM,0)),1) 部门平均资金 
         FROM EMP E RIGHT JOIN DEPT D
         ON E.DEPTNO=D.DEPTNO 
         GROUP BY DNAME,D.DEPTNO 
         HAVING D.DEPTNO IN(10,20,30) 
         ORDER BY D.DEPTNO;
    
    部门名称         部门编号     部门总人数   部门平均工资 部门平均资金
    -------------- ---------- ---------- ------------ ------------
    ACCOUNTING             10          3      2916.67            0
    RESEARCH               20          5         2175            0
    SALES                  30          6      1566.67        366.7
    
    SQL>

解析：使用NVL函数处理空值，使空值为0，因为AVG函数在计算平均值时会忽略空值。使用ROUND函数来进行四舍五入。使用HAVING子句来过滤分组结果，所以having不能放在order by条件之后。同理可使用SUM、MAX、MIN函数来进行统计。

SQL> SELECT DNAME 部门名称,D.DEPTNO 部门编号,COUNT(ENAME) 部门总人数,ROUND(AVG(NVL(SAL,0)),2) 部门平均工资,ROUND(AVG(NVL(COMM,0)),1) 
    部门平均资金,NVL(MAX(SAL),0) 最高工资,NVL(MIN(SAL),0) 最低工资,NVL(SUM(SAL),0) 部门工资总支出,NVL(MAX(COMM),0) 最高资金, NVL(MIN(COMM),0) 
    最低奖金,NVL(SUM(COMM),0)  部门奖金总额
    FROM EMP E RIGHT JOIN DEPT D 
    ON E.DEPTNO=D.DEPTNO 
    GROUP BY DNAME,D.DEPTNO  
    ORDER BY D.DEPTNO;
    
    部门名称   部门编号 部门总人数  部门平均工资  部门平均资金  最高工资  最低工资  部门工资总支出  最高资金 最低奖金  部门奖金总额
    --------- -------- ---------- ---------  -------   ------     ---------- ------- ---------- -------- ----------
    ACCOUNTING  10          3      2916.67       0       5000       1300       8750          0     0            0
    RESEARCH    20          5         2175       0       3000        800      10875          0     0            0
    SALES       30          6      1566.67   366.7       2850        950       9400       1400     0         2200
    OPERATIONS  40          0            0       0          0          0          0          0     0            0
    
    SQL>