php - group by后排序问题

Question

1.针对一个文章，我有N多的评论
2.现在我想获取到每篇文章最新的评论
这个子查询是可以的 ，但是要求是不用子查询，如何实现呢
SELECT FROM (SELECT FROM old_chapter_check_list ORDER BY id DESC) AS t GROUP BY cid LIMIT 10

Answer 1

According to your description, your sql seems to be unsatisfactory.
Two ways of writing
1. Be sure not to use subqueries. If you confirm that the comment does not contain some kind of delimiter (such as "|||"), use substring_index(group_concat)) Function processing:

select substring_index(group_concat(comment_content order by id desc separator '|||'),'|||',1) cmt,cid from old_chapter_check_list group by cid limit 10;

2. If you are not sure whether it contains special characters, first take out the latest id values ​​of the 10 articles with the latest comments (if the only auto-incremented primary key) and then perform a joint query

select a.cid,a.comment_content from old_chapter_check_list a join (select max(id) maxid from old_chapter_check_list group by cid order by maxid desc limit 10) b on a.id=b.maxid;

Answer 2

SELECT * FROM old_chapter_check_list GROUP BY cid ORDER BY id DESC LIMIT 10

使用多次查询

SELECT max(id) FROM old_chapter_check_list GROUP BY articleid

查询出每篇文件评论的最大id

根据前面的结果集，在循环中查询最大id对应的评论

select * from old_chapter_check_list where cid = maxid

Answer 3

Write a view and take it directly from the view

Answer 4

The student list is based on the 2 older students in each class

1、select * from tb A where id in (select id from tb B where A.class = B.class order by age desc limit 2) 

2、select * from tb B where 2 > (select count(*) from tb B where A.class = B.class and B.age > A.age)

3、select A.id,A.name,A.age,A.class from group_by_test A left join group_by_test B on A.class = B.class and A.age < B.age group by A.name,A.age,A.class having count(B.age)<2

Answer 5

That’s it, the id will increase automatically and the cid will increase automatically

SELECT *,MAX(id) max_id FROM old_chapter_check_list GROUP BY cid order by max_id LIMIT 10