search

Home  >  Q&A  >  body text

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

Forum section table:

Forum post table:

Rendering:

ringa_leeringa_lee2754 days ago1011

reply all(5)I'll reply

  • 给我你的怀抱

    给我你的怀抱2017-06-29 10:10:50

    Refer to this

    reply
    0
  • 为情所困

    为情所困2017-06-29 10:10:50

    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 :-)

    reply
    0
  • 淡淡烟草味

    淡淡烟草味2017-06-29 10:10:50

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

    reply
    0
  • 扔个三星炸死你

    扔个三星炸死你2017-06-29 10:10:50

    If you have to use one sentence, use union

    reply
    0
  • 学习ing

    学习ing2017-06-29 10:10:50

    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:

    1

    2

    3

    4

    5

    6

    7

    8

    <code>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;

    </code>

    If the order is inconsistent, use the following:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    <code>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;</code>

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

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    <code>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;</code>

    reply
    0
  • Cancelreply