sql - SQL 查询中每个标题只显示一行

Question

我有以下查询：

select distinct p.title, e.first_name, e.last_name, max(e.salary)
 from employees as e
 inner join employees_projects as ep
 on e.id = ep.employee_id
 inner join projects as p
 on p.id = ep.project_id
 group by 1,2,3
 order by p.title

每个标题返回多行。我只想要每个职位的最高薪水。

          title           | first_name | last_name |  max  
--------------------------+------------+-----------+-------
 Build a cool site        | Cailin     | Ninson    | 30000
 Build a cool site        | Ian        | Peterson  | 80000
 Build a cool site        | Mike       | Peterson  | 20000
 Design 3 New Silly Walks | Ava        | Muffinson | 10000
 Update TPS Reports       | John       | Smith     | 20000

调整 @zealous 代码，这有效：

 select
    title,
    first_name, 
    last_name,
    salary
from
(select 
    distinct p.title, 
    e.first_name, 
    e.last_name,
    e.salary,
    dense_rank() over (partition by p.title order by e.salary desc) as rnk
 from employees as e
 inner join employees_projects as ep
 on e.id = ep.employee_id
 inner join projects as p
 on p.id = ep.project_id
 group by 1,2,3, 4
 ) t
 where rnk = 1
 order by title

标签： sqlpostgresql

Answer 1

试试这个窗口功能dense_rank()。如果工资中存在领带关系，那么它将返回具有最高工资的两条记录。

如果您只想要一条最高薪水的记录，请使用row_number().

select
    title,
    first_name, 
    last_name,
    salary
from
(select 
    distinct p.title, 
    e.first_name, 
    e.last_name,
    salary,
    dense_rank() over (partition by title order by salary desc) as rnk
 from employees as e
 inner join employees_projects as ep
 on e.id = ep.employee_id
 inner join projects as p
 on p.id = ep.project_id
 group by 1,2,3
 ) t
 where rnk = 1
 order by title