试试这个糟糕的SQL代码:)
select post_id, user_id, post_datetime, post_text from ( select posts.*, if (user_id = @prev_user and date(post_datetime) = date(@prev_day), @row := @row + 1, @row := 1) idx, @prev_user := user_id, @prev_day := post_datetime from posts, (select @row := 1, @prev_user := null, @prev_day := null) init order by date(post_datetime), user_id, post_datetime desc) swhere s.idx <= 2
结果：
+---------+---------+---------------------------------+----------------+| POST_ID | USER_ID | POST_DATETIME | POST_TEXT |+---------+---------+---------------------------------+----------------+| 4 | 100 | December, 01 2012 04:00:00+0000 | lorem ipsum 4 || 2 | 100 | December, 01 2012 02:00:00+0000 | lorem ipsum 2 || 3 | 101 | December, 01 2012 03:00:00+0000 | lorem ipsum 3 || 5 | 102 | December, 01 2012 05:00:00+0000 | lorem ipsum 5 || 6 | 100 | December, 02 2012 03:00:00+0000 | lorem ipsum 6 || 10 | 101 | December, 02 2012 07:00:00+0000 | lorem ipsum 10 || 9 | 101 | December, 02 2012 06:00:00+0000 | lorem ipsum 9 || 7 | 102 | December, 02 2012 04:00:00+0000 | lorem ipsum 7 |+---------+---------+---------------------------------+----------------+
在这里摆弄。
我认为如果按日期降序排序会更合适，因为实际上是最接近当前日期的前2位。
