mysql - 如何在不使用子查询的情况下过滤重复的数据

This question already has answers here:

How to use Group By and self-join to return min, max, open, and close daily price restult set?
                                
                                    （2个答案）
                                
                        
                        
                            SQL select only rows with max value on a column [duplicate]
                                
                                    （27个答案）
                                
                        
                                2年前关闭。
            
                    
数据

id   amount created_at
"1" "105"   "2018-01-28 15:22:00"
"2" "120"   "2018-01-28 15:23:00"
"3" "200"   "2018-01-28 15:24:00"
"4" "205"   "2018-01-28 15:24:10"
"5" "230"   "2018-01-28 15:25:00"

这是我的表字段和数据，我的cronjob每分钟运行一次，但是由于某种原因，它可能在同一分钟再次运行，因此我需要按日期将数据组过滤为分钟

我尝试了什么

SELECT COUNT(*), SUM(`amount`), MAX(`amount`), MIN(`amount`)
FROM (
SELECT *
FROM `stats`
GROUP BY DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i')
) AS tmp
GROUP BY DATE_FORMAT(`created_at`, '%Y-%m-%d %H:%i')

结果正是我所需要的，但是我听说由于效率问题，子查询始终是最后的选择，还有另一种方法吗？

"1" "105"   "105"   "105"
"1" "120"   "120"   "120"
"1" "200"   "200"   "200"
"1" "230"   "230"   "230"

最佳答案

您的预期输出意味着您希望每分钟记录一次，并希望该分钟的ID或金额最小的记录。假设您想要最小的ID，则可以尝试以下查询：

SELECT s1.*
FROM stats s1
INNER JOIN
(
    SELECT
        DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS created_at
        MIN(id) AS min_id
    FROM stats
    GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H:%i')
) s2
    ON DATE_FORMAT(s1.created_at, '%Y-%m-%d %H:%i') = s2.created_at AND
       s1.amount = s2.min_id;

关于mysql - 如何在不使用子查询的情况下过滤重复的数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48484248/