将查询结果限制为每组两行 [英] Limit query results to two rows per group

问题描述

我有一个查询列出所有帖子:

I have a query to list all posts:

SELECT *, DATE(FROM_UNIXTIME(`timestamp`)) `date` 
FROM `posts`
ORDER BY `date` DESC

查询列出所有行，我只想为每个date显示最后2条帖子，我该怎么做?

The query list all rows, I want to only show last 2 post for each date, how can I do this?

推荐答案

这可能有用，尽管我不能说太多有关性能的信息...

This might work, though I couldn't say much about it's performance...

SELECT DATE(FROM_UNIXTIME(MyTimestamp)) AS ForDate, *
FROM   MyPostsTable
WHERE  2 >=
(
    SELECT COUNT(*)
    FROM   MyPostsTable AS lookup
    WHERE  DATE(FROM_UNIXTIME(lookup.MyTimestamp)) = DATE(FROM_UNIXTIME(MyPostsTable.MyTimestamp))
    AND    lookup.MyTimeStamp >= MyPostsTable.MyTimestamp
)

这篇关于将查询结果限制为每组两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！