案例:
table1
需求:
60天内获取每天日期最新的那条数据集合?
方案:
SELECT
s.date_time AS DATETIME,
s.down_limit AS downLimit,
s.up_limit AS upLimit
FROM
stock_up_down_distribute s
RIGHT JOIN
(SELECT
MAX(date_time) AS maxTime
FROM
stock_up_down_distribute
GROUP BY day_time) AS t
ON t.maxTime = s.date_time
ORDER BY s.date_time
LIMIT 60
由于 GROUP BY 和 order by 的顺序问题得到需求理想的结果。 我们先 GROUP BY day_time 得到 MAX(date_time) 最新数据的临时表 ,再right join table1
完美解决问题。