php – 我想改进的非常具体的MySQL查询

[weixin_38115748]

这是我的场景：我有一个包含事件的表,每个事件都有一个名为“created”的字段,其中包含创建该事件的时间戳.现在我需要将事件从最新到最旧排序,但我不希望MySQL将它们全部返回.我只需要给定间隔内的最新版本,例如24小时内(编辑：我想要一个灵活的解决方案,不仅是24小时范围,而且可能每隔几个小时).我只需要过去10天.我已经实现了这一点,但我确信以最低效的方式,即类似的东西： $timestamp = time(); for($i = 0; $i < 10; $i++) { $query = "SELECT * FROM `eventos` WHERE ... AND `created` < '{$timestamp}' ORDER BY `created` DESC LIMIT 1"; $return = $database->query( $query ); if($database->num( $return ) > 0) { $event = $database->fetch( $return ); $events[] = $event; $timestamp = $timestamp - 86400; } } 我希望我很清楚.谢谢,耶稣.

[weixin_38120031]

假设您想要过去10天内每天最新(创建日期最长)事件. 所以让我们每天获得最新的时间戳 $today = date('Y-m-d'); $tenDaysAgo = date('Y-m-d', strtotime('-10 day')); $innerSql = "SELECT date_format(created, '%Y-%m-%d') day, MAX(created) max_created FROM eventos WHERE date_format(created, '%Y-%m-%d') BETWEEN '$today' and '$tenDaysAgo' GROUP BY date_format(created, '%Y-%m-%d')"; 然后我们可以选择与创建日期匹配的所有事件 $outerSql = "SELECT * FROM eventos INNER JOIN ($innerSql) as A WHERE eventos.created = A.max_created"; 我没有机会测试这个,但原则应该足够健全. 如果要按其他任意小时数分组,可以更改innerSql： $fromDate = '2012-07-06' // or if you want a specific time '2012-07-06 12:00:00' $intervalInHours = 5; $numberOfIntervals = 10; $innerSql = "SELECT FLOOR(TIMESTAMPDIFF(HOUR, created, '$fromDate') / $intervalInHours) as grouping, MAX(created) as max_created FROM eventos WHERE created BETWEEN DATE_SUB('$fromDate', INTERVAL ($intervalInHours * $numberOfIntervals) HOUR) AND '$fromDate' GROUP BY FLOOR(TIMESTAMPDIFF(HOUR, created, '$fromDate') / $intervalInHours)";