php - Use a sql to query the latest 6 posts under each section of the forum

Question

Forum section table:

Forum post table:

Rendering:

Answer 1

Refer to this

Answer 2

If one SQL statement cannot do this, it is recommended to loop through all sections and use SELECT ... WHERE fid = ? ORDER BY dateline LIMIT 6 to get the latest 6 posts. To improve efficiency, (fid, dateline) Can be made into a composite index.

In addition, it is possible to use a SQL statement to find out the latest 1 posts in each forum, but it is not easy. Give it a try :-)

Answer 3

Use union, and then (fid, dateline) plus the joint index

Answer 4

If you have to use one sentence, use union

Answer 5

If there are many sections, union is more troublesome. The following SQL can get the result
If your tid and dateline are in the same order, you can write like this:

select * 
from t_tbl a
where 
    (select count(1) 
     from t_tbl b
     where b.fid=a.fid and a.tid>b.tid)<6 
order by fid,tid;

If the order is inconsistent, use the following:

select aa.* 
from 
    (select fid,tid,title,content,dateline,(@rownum:=@rownum+1) rn 
     from t_tbl,(select @rownum:=1) a 
     order by fid,dateline) aa 
where 
    (select count(1) 
     from 
         (select fid,tid,title,content,dateline,(@rownum:=@rownum+1) rn 
          from t_tbl,(select @rownum:=1) a 
          order by fid,dateline) bb 
     where bb.fid=aa.fid and aa.rn>bb.rn)<6;

···························Separating line··················· ···············
In addition, you can also introduce the line number within the group, which seems to be easier:

select 
    fid,title,content,dateline 
from (
    select 
        @gn:=case when @fid=fid then @gn+1 else 1 end gn,
        @fid=fid fid,
        title,
        content,
        dateline
     from t_tbl,(select @gn:=1) a
     order by fid,dateline) aa
where gn<7;