时间戳

首页 > 解决方案 > Oracle 分析函数与 group by

oracle - Oracle 分析函数与 group by

问题描述

通常我们按部门名称进行分组并找到最高和最低工资。

select dept_name,sum(salary),max(salary).min(salary) from emp group by dept_name;

但是我怎样才能找到获得最高薪水和最低薪水的员工的姓名。是否可以在单个 sql 语句中找到？

标签： oracleoracle11goracle10g

解决方案

您可以使用FIRST功能：

SELECT dept_name, SUM(salary), MAX(salary), MIN(salary),
    MAX(emp_name) KEEP (DENSE_RANK FIRST ORDER BY salary) AS Min_salary_emp,
    MAX(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) as  Max_salary_emp 
FROM emp 
GROUP BY dept_name;

请注意，如果您有不止一名员工具有相同的薪水，您只会得到其中一名。

根据您的要求，您还可以使用模式匹配

SELECT dept_name, MAX_salary, MIN_salary, salary, emp_name,
    Min_salary_emp, Max_salary_emp
FROM emp
MATCH_RECOGNIZE (
    PARTITION BY dept_name
    ORDER BY salary
    MEASURES 
        FINAL MIN(salary) AS MIN_salary,
        FINAL MAX(salary) AS MAX_salary,
        a.emp_name AS Min_salary_emp,
        c.emp_name AS Max_salary_emp
    ALL ROWS PER MATCH
    PATTERN ( (a+ {- b* -} c+) | a )
    DEFINE
    a AS salary = MIN(salary),
    c AS salary = MAX(salary)
);

推荐阅读