Oracle-查询-分组函数练习

谁借莪１个温暖的怀抱￠ 2022-04-03 14:16 302阅读 0赞

**分组函数不能在where关键字后面使用**

**分组函数自动忽略空值**

-- 查询 employees 表中有多少个部门
    SELECT e.department_id
    FROM employees e
    WHERE e.department_id IS NOT NULL
    GROUP BY e.department_id;
    -- HAVING e.department_id IS NOT NULL;

-- 查询全公司奖金基数的平均值(没有奖金的人按 0 计算)
    SELECT AVG(NVL(e.commission_pct,0))
    FROM employees e
    -- 结果为：0.0728971962616822
    SELECT AVG(e.commission_pct)
    FROM employees e;
    -- 结果为：0.222857142857143

-- 查询各个部门的平均工资
    SELECT e.department_id,AVG(e.salary)
    FROM employees e
    WHERE e.department_id IS NOT NULL
    GROUP BY e.department_id

-- Toronto这个城市的员工的平均工资
    SELECT AVG(e.salary)
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    JOIN locations l
    ON d.location_id = l.location_id
    WHERE l.city = 'Toronto'
    -- GROUP BY l.city

-- (有员工的城市)各个城市的平均工资
    SELECT l.city,AVG(e.salary)
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    JOIN locations l
    ON d.location_id = l.location_id
    GROUP BY l.city

-- 查询平均工资高于 8000 的部门 id 和它的平均工资
    SELECT e.department_id,AVG(e.salary)
    FROM employees e
    GROUP BY e.department_id
    HAVING AVG(e.salary) > 8000;

-- 查询平均工资高于 6000 的 job_title 有哪些
    SELECT j.job_title,AVG(e.salary)
    FROM employees e
    JOIN jobs j 
    ON e.job_id = j.job_id
    GROUP BY j.job_title
    HAVING AVG(e.salary) > 6000;

-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
    SELECT  m.employee_id,MIN(e.salary) min_sal
    FROM employees e
    JOIN employees m
    ON e.manager_id = m.employee_id
    GROUP BY m.employee_id
    HAVING MIN(e.salary) > 6000

-- 谁的工资比 Abel 高?
    SELECT e.last_name,e.salary
    FROM employees e
    WHERE e.salary > (
                     SELECT emp.salary
                     FROM employees emp
                     WHERE emp.last_name = 'Abel'
                   );

-- 查询所有部门的名字，location_id，员工数量和工资平均值
    SELECT e.department_id,d.location_id,COUNT(*) emp_num,AVG(e.salary)
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    GROUP BY e.department_id,d.location_id