php - groupby後排序問題

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

按你的描述你的sql貌似並不滿足呢
兩種寫法
1、一定不要子查詢的，確認評論中不包含某種分隔符的話(比如“|||”)使用substring_index(group_concat))函數處理：

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、不確定是否包含特殊字元的寫法，先取出有最新評論的10個文章最新id值(唯一自增主鍵的話)再進行聯表查詢

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

雷雷

Answer 3

寫個視圖直接從視圖取

Answer 4

學生表取各班級年齡較大的2名

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

這樣就可以了，id自增，cid自增的狀況

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