我有一个用户帖子表。我需要显示从1到最大N张贴从每个用户每一天。
例子：
post_id|user_id|post_datetime|post_text
1 |100 |2012-12-01 01:00:00|lorem ipsum 1
2 |100 |2012-12-01 02:00:00|lorem ipsum 2
3 |101 |2012-12-01 03:00:00|lorem ipsum 3
4 |100 |2012-12-01 04:00:00|lorem ipsum 4
5 |102 |2012-12-01 05:00:00|lorem ipsum 5
6 |100 |2012-12-02 03:00:00|lorem ipsum 6
7 |102 |2012-12-02 04:00:00|lorem ipsum 7
8 |101 |2012-12-02 05:00:00|lorem ipsum 8
9 |101 |2012-12-02 06:00:00|lorem ipsum 9
10 |101 |2012-12-02 07:00:00|lorem ipsum 10
我需要一个查询，返回每个用户每天2个POST的最大值：
post_id|user_id|post_datetime|post_text
2 |100 |2012-12-01 02:00:00|lorem ipsum 2
4 |100 |2012-12-01 04:00:00|lorem ipsum 4
3 |101 |2012-12-01 03:00:00|lorem ipsum 3
5 |102 |2012-12-01 05:00:00|lorem ipsum 5
6 |100 |2012-12-02 03:00:00|lorem ipsum 6
7 |102 |2012-12-02 04:00:00|lorem ipsum 7
9 |101 |2012-12-02 06:00:00|lorem ipsum 9
10 |101 |2012-12-02 07:00:00|lorem ipsum 10
我试过组队，但是我只得到前n个记录，而不是每个用户每天的前n个：
SELECT a.* FROM posts AS a
JOIN posts AS a2
ON a.user_id = a2.user_id AND a.post_datetime <= a2.post_datetime
GROUP BY a.post_id
HAVING COUNT(*) <= 3
ORDER BY a.post_id, a.post_datetime DESC
最佳答案
试试这个糟糕的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
) s
where 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 |
+---------+---------+---------------------------------+----------------+
小提琴here。
我认为如果按日期降序的话，排序会更合适，因为您实际上得到了最接近当前日期的前2个。
关于mysql - MySQL:我每天需要从每个用户的1个帖子到最多n个帖子显示，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13811878/