Mysql查找一个列的数据改变的唯一记录

Question

这是一个表格

tbody> <<tr>

Clientid	NId	状态	LastUpdatedById	AuditCreatedDate
26655777 - 9177 - 49 - b9 - 9 - ac0 - 0 - ac893fbde7a	56565	新	3604561	2022-05-11 16:18:54
26655777 - 9177 - 49 - b9 - 9 - ac0 - 0 - ac893fbde7a	56565	新	3604561	2022-05-11 16:18:51
26655777 - 9177 - 49 - b9 - 9 - ac0 - 0 - ac893fbde7a	56565	停了	3604561	2022-05-11 16:18:40
26655777 - 9177 - 49 - b9 - 9 - ac0 - 0 - ac893fbde7a	56565	新	4376987	2022-05-11 14:54:18
26655777 - 9177 - 49 - b9 - 9 - ac0 - 0 - ac893fbde7a	56565	新	200161	2021-06-20 10:20:15
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	停了	3604561	2022-05-11 22:02:08
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	停了	3604561	2022-05-11 17:19:52
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	新	3604561	2022-05-11 17:19:43
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	停了	3604561	2022-05-11 17:10:14
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	停了	3604561	2022-05-11 16:19:19
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	新	123456	2021-05-18 10:20:17
35 ad85c1-d986-48d1-a1cb-e8bbbe921611	2126335	新	123456	2021-03-04 14:20:58
5 eac9c0c - 057 d - 4926 bf56 - 394 d36c51f04	2125219	停了	3604561	2022-05-11 16:29:03
5 eac9c0c - 057 d - 4926 bf56 - 394 d36c51f04	2125219	停了	3604561	2022-05-11 16:19:03
5 eac9c0c - 057 d - 4926 bf56 - 394 d36c51f04	2125219	新	123456	2021-04-20 10:22:20
5 eac9c0c - 057 d - 4926 bf56 - 394 d36c51f04	2125219	新	54321	2021-03-25 20:58:52
5 eac9c0c - 057 d - 4926 bf56 - 394 d36c51f04	2125219	新	123456	2021-03-04 14:20:44

这是不理想的，因为你使用的是MySQL 5.7，没有能力使用窗口函数，但这可以产生你想要的结果:

select t1.*
from mytable as t1
join mytable as t2
on t2.auditcreateddate < t1.auditcreateddate
and t2.status = 'New'
left outer join mytable as t3
on t3.auditcreateddate < t1.auditcreateddate
and t3.auditcreateddate > t2.auditcreateddate
where t1.status = 'Paused'
and t3.status IS NULL;

https://www.db-fiddle.com/f/21BLqr66LJxupaGY6gEWB9/0