php – Verwenden Sie eine SQL, um die letzten 6 Beiträge in jedem Abschnitt des Forums abzufragen
ringa_lee
ringa_lee 2017-06-29 10:08:50
0
5
995

Forum-Abschnittstabelle:

Forum-Beitragstabelle:

Rendering:

ringa_lee
ringa_lee

ringa_lee

Antworte allen(5)
给我你的怀抱

参考这个

为情所困

一条SQL语句做不到的,建议循环遍历所有版块,每个版块用 SELECT ... WHERE fid = ? ORDER BY dateline LIMIT 6 得到最新6条帖子,为提高效率,(fid, dateline)可以做成复合索引。

另外,用一条SQL语句查出每个版块最新的1条帖子,是能实现的,但不是件容易的事,试试看吧 :-)

淡淡烟草味

用union,然后(fid, dateline)加上联合索引

扔个三星炸死你

非要一条语句的话 用 union

学习ing

板块很多的话union比较麻烦,下面一条sql可以得到结果
如果你的tid和dateline顺序一致的话可以这么写:

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;

顺序不一致就用下面的:

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;

··························分割线···································
补充一下,还可以引入组内行号,好像更简便一些:

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;
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage