mysql - SQL分组排序、随机问题？

Question

有一个帖子的回复表，
posts（id ， tid ， subject ，message ，dateline），
其中：id为自动增长字段，tid为该回复的主体帖子的id（外键关联），subject为回复标题，message为回复内容，dateline为回复时间，用UNIX时间戳表示。
请写SQL，

1. 选出按时间排序的前十个来自不同主题的最新回复；

2. 随机选一条回复记录；

Answer 1

Visual inspection of the first sentence on the first floor is wrong. There is no guarantee of different topics. Here is what I wrote:

<code>select * from posts p where p.dateline = (select max(dateline) from posts where tid = p.tid) order by dateline desc limit 10;</code>

In fact, dateline and id should be in the same order. You can consider changing to id. The primary key on id should be more efficient:

<code>select * from posts p where p.id = (select max(id) from posts where tid = p.tid) order by id desc limit 10;</code>

In the second sentence, I personally think it is better not to use ORDER BY RAND(). A slightly larger amount of data will be inefficient, because writing this way may lead to multiple scans. It may be better to write it this way:

<code>SELECT * FROM posts WHERE id >= ((SELECT MAX(id) FROM posts)-(SELECT MIN(id) FROM posts)) * RAND() + (SELECT MIN(id) FROM posts)  LIMIT 1;</code>

Answer 2

Try it. If the table data is too large, it is not recommended to use ORDER BY RAND(). Please try these two sentences SQL first to see if they work. Hope it helps you.

<code>SELET `id`,`tid`,`subject`,`message`,`dateline` FROM `posts` WHERE `tid`=NULL ORDER BY `tid`,`dateline`

SELET `id`,`tid`,`subject`,`message`,`dateline` FROM `posts` WHERE `tid`=!NULL ORDER BY RAND() LIMIT 1</code>

Answer 3

The first answer upstairs is not correct by visual inspection. The following is my personal opinion. Comments and advice are welcome

<code>select t2.* from (select tid,max(dateline) maxDateline from posts group by tid order by maxDateline desc limit 10)t1 inner join posts t2 on t1.tid=t2.tid and t1.maxDateline=t2.dateline</code>