代码之家 › 专栏 › 技术社区 › Abdul Raheem Ghani

PostgreSQL查询有问题

greatest-n-per-group postgresql sql

-1

Abdul Raheem Ghani · 技术社区 · 6 年前

我有以下表格:

create table employee(id int, name varchar(50), fname varchar(50));

create table rank (id int, name varchar(50));

create table promotion(id int, dt date, from_rank_id int, to_rank_id int, employee_id int,
constraint fk_pro_emp foreign key(employee_id) references employee(id),
constraint fk_pro_rank_f foreign key(from_rank_id) references rank(id),
constraint fk_pro_rank_t foreign key(to_rank_id) references rank(id));

insert into employee values(1, 'John', 'Roy'), (2, 'Kane', 'Williamson'), (3, 'Yasin', 'Khan'), (4, 'Dwayne', 'Brain');

insert into rank values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

--One person can have many promotions so I insert two records for the same person
insert into promotion values(1, '2010-01-01', 1, 2, 1), (2, '2015-01-01', 2, 3, 1);

--I insert three promotions for the second employee
insert into promotion values(4, '2011-11-23', 1, 2, 2), (5, '2012-04-05', 2, 3, 2), (6, '2013-12-30', 3, 4, 2);

--I insert one record for the third person
insert into promotion values(7, '2015-10-21', 3, 4, 3);

--The last person does not have promotion

现在,我想从员工中选择记录以及他们最近(最长提升日期)的提升记录。
所需输出为:

EMP_ID      Name      Father_Name     Pro_Date    From_Rank    To_Rank  
   1        John         Roy          2015-01-01     B            C  
   2        Kane       Williamson     2013-12-30     C            D  
   3        Yasin        Khan         2015-10-21     C            D  
   4        Dwayne       Brain         <Null>       <Null>      <Null>

事先谢谢你的帮助。

3 回复 | 直到 6 年前

a_horse_with_no_name 6 年前

通常的解决方法 greatest-n-per-group Postgres中的问题是使用(专有) distinct on() 操作员。

以下查询返回每个员工的最新晋升:

select distinct on (p.employee_id) p.employee_id
from promotion p
order by p.employee_id, p.dt desc

这可以与 rank 获取排名名称的表:

select distinct on (p.employee_id) p.employee_id, p.dt as promotion_date, fr.name as from_rank, tr.name as to_rank
from promotion p
  join rank tr on tr.id = p.to_rank_id
  join rank fr on fr.id = p.from_rank_id
order by p.employee_id, p.dt desc

您的表设置允许在没有 from_rank_id 或 to_rank_id 因为这些列可以为空。如果确实允许这样做(我对此表示怀疑),那么您需要将这些联接更改为外部联接。至少 等级 应定义为 NOT NULL 但可能两者都有。

该查询反过来可以加入到 employee 桌子:

select e.*, t.*
from employee e 
  left join (
     select distinct on (p.employee_id) p.employee_id, p.dt as promotion_date, fr.name as from_rank, tr.name as to_rank
     from promotion p
       join rank tr on tr.id = p.to_rank_id
       join rank fr on fr.id = p.from_rank_id
     order by p.employee_id, p.dt desc
  ) t on t.employee_id = e.id
order by e.id;

在线示例: http://rextester.com/NWBDFL38394

sticky bit 6 年前

您可以使用 LATERAL 联接和子查询,选择最大的提升 dt 对于给定的 employee_id ORDER BY dt DESC LIMIT 1 获取一次晋升的员工和晋升数据。 UNION ALL 从未晋升过的员工,您可以向 NOT EXISTS (SELECT * FROM promotion ...) .

SELECT e.id "EMP_ID",
       e.name "Name",
       e.fname "Father_Name",
       x.dt "Pro_Date",
       x.rank_before "From_Rank",
       x.rank_after "To_Rank"
       FROM employee e
            CROSS JOIN LATERAL (SELECT p.dt,
                                       rb.name rank_before,
                                       ra.name rank_after
                                       FROM promotion p
                                            LEFT JOIN rank rb
                                                      ON rb.id = p.from_rank_id
                                            LEFT JOIN rank ra
                                                      ON ra.id = p.to_rank_id
                                       WHERE p.employee_id = e.id
                                       ORDER BY p.dt DESC
                                       LIMIT 1) x
UNION ALL
SELECT e.id "EMP_ID",
       e.name "Name",
       e.fname "Father_Name",
       NULL "Pro_Date",
       NULL "From_Rank",
       NULL "To_Rank"
       FROM employee e
       WHERE NOT EXISTS (SELECT *
                                FROM promotion p
                                WHERE p.employee_id = e.id);

Ankit Bajpai 6 年前

试试这个-

SELECT e.id, e.name, e.fname, p3.dt pro_date, p3.name2 From_Rank, p3.name1 To_Rank
FROM employee e
left join (select p.dt dt, p.employee_id, r.name name1, r2.name name2
           from promotion p
           inner join (select max(dt) dt, employee_id
                       from promotion
                       group by employee_id) p2
                       on p.dt = p2.dt
          left join rank r on p.to_rank_id = r.id
          left join rank r2 on p.from_rank_id = r2.id) p3 
on e.id = p3.employee_id

http://www.sqlfiddle.com/#!17/bd7e4/50