mysql - 仅通过创建的 Mysql 查询过滤重复记录

Question

我尝试使用带有 where 子句的 select 查询来获取记录，但出现了 id 重复记录，然后我想通过 createdAt 字段对其进行过滤。

当我运行以下查询并得到如下结果时:

select * from security_development.user_requests
where
fully_approved = 1 and user_request_role=4

请查找结果

createdAt           User_request_name fully_approved user_request_role
2019-02-22 18:57:08  dealerAdmin        1               4
2019-02-24 08:57:08  ask1               1               4
2019-02-25 12:20:22  dealerAdmin        1               4

如果我尝试通过使用 max(creaTed_at) 作为子查询来避免重复的 User_request_name，

select * from security_development.user_requests
where fully_approved =1 and user_request_role=4
created_at = 
(select max(creaTed_at) from security_development.user_requests group by user_request_name having count(user_request_name) > 1 )

然后我得到以下结果

createdAt           User_request_name fully_approved user_request_role
2019-02-25 12:20:22  dealerAdmin        1               4

但我期待以下结果

createdAt           User_request_name fully_approved user_request_role
2019-02-24 08:57:08  ask1               1               4
2019-02-25 12:20:22  dealerAdmin        1               4

Answer 1

您可以使用相关子查询

select t1.* from security_development.user_requests t1
where
fully_approved = 1 and user_request_role=4
and createdAt=( select max(createdAt) 
              from security_development.user_requests t2
                where t1.User_request_name=t2.User_request_name)