php - 从 20 个一组中选择最多 5 个整数

Question

我正在尝试从表格中最近 20 篇条目的列表中选择 5 篇查看最多的文章。我的表结构基本上是这样的:

id | date | title | content | views

我的第一个想法是使用内部选择来获取最近的 20 篇文章，然后从中选择，但我还没有运气。

//doesn't work (my version of mysql doesn't support LIMIT in sub queries)
$recent = "(SELECT id FROM news ORDER BY date DESC LIMIT 20)";
$result = $db->query("SELECT id, title, date, content FROM news WHERE id IN $recent ORDER BY views DESC LIMIT ".self::RECENT_MAX);

//neither does this (syntax error @ 'OFFSET 20')
$recent = "(SELECT MAX(date) FROM news ORDER BY date DESC OFFSET 20)";
$result = $db->query("SELECT id, title, date, content FROM news WHERE date > $recent ORDER BY views DESC LIMIT ".self::RECENT_MAX);

有人对如何构造此查询有任何建议吗？

Answer 1

我刚刚测试了这个并且它有效

SELECT *
FROM (
    SELECT *
    FROM news
    ORDER BY id DESC
    LIMIT 0, 20
) lasttwenty
ORDER BY views DESC
LIMIT 0, 5

服务器版本:5.0.51a-3ubuntu5.4