通过筛选状态查找最新的行

Question

我有一个名为person_list的表。数据是，

Insert into person_list(person_allocation_id, person_id, created_datetime, boss_user_name, allocation_status_id) values
(111008, 1190016, '2021-01-05 11:09:25', 'Rajesh', '2'),
(111007, 1190015, '2020-12-12 09:23:31', 'Sushmita', '2'),
(111006, 1190014, '2020-12-11 10:48:26', '', '3'),
(111005, 1190014, '2020-12-10 13:46:15', 'Rangarao', '2'),
(111004, 1190014, '2020-12-10 13:36:10', '', '3');

这里person_allocation_id是主键。

person_id可能重复一些次。

所有这些行都按person_allocation_id(按降序(进行排序

现在，我想过滤具有allocation_statuss_id='2'并且boss_user_name对于person_id应该为非空的行。

这里的困难在于，如果person_id的最新状态为allocation_status_id='3'(根据日期(，则我必须排除该行。

我无法理解如何将一行的日期与前一行的另一行进行比较。

所以最后我应该在我的最终结果集中只得到2行(person_allocation_id是111008和111007(。

不知怎的，我在甲骨文公司做到了这一点。

select person_id, person_allocation_id, create_datetime, boss_user_name, allocation_status_id 
from (
select person_id, person_allocation_id, create_datetime, boss_user_name, allocation_status_id, 
rank() over (partition by person_id order by create_datetime desc) rnk
from person_list 
where allocation_status_id = '2') 
where rnk = 1;

但是，我需要这个MySql数据库。有人帮忙吗？

谢谢。

Answer 1

SELECT t1.*
FROM person_list t1
JOIN ( SELECT MAX(t2.person_allocation_id) person_allocation_id, t2.person_id
FROM person_list t2
GROUP BY t2.person_id ) t3 USING (person_allocation_id, person_id)
WHERE t1.allocation_status_id = '2'

小提琴

如果需要，可以向WHERE子句添加更多条件(例如，AND boss_user_name != ''(。

Answer 2

您可以使用相关子查询来获取每个person_id:的最新allocation_status_id值

select person_allocation_id   
, person_id
, created_datetime
, boss_user_name
, allocation_status_id
from (   
select person_allocation_id   
, person_id
, created_datetime
, boss_user_name
, allocation_status_id
, (select pl2.allocation_status_id
from person_list pl2
where pl2.person_id = pl.person_id
order by pl2.created_datetime desc
limit 1) latest_allocation_status_id
from person_list pl) t
where 
allocation_status_id = '2' and latest_allocation_status_id <> '3' 
and boss_user_name <> ''

外部查询能够检查最新状态并返回期望的结果集。该查询适用于MySQL 5.7

在这里演示

附带说明一下，对于MySQL 8.0，您可以用窗口函数替换相关的子查询：

last_value(allocation_status_id) over (partition by person_id 
order by created_datetime desc)

窗口功能演示