获取每组的前/后n条记录

I have two tables : tableA (idA, titleA) and tableB (idB, idA, textB) with a one to many relationship between them. For each row in tableA, I want to retrieve the last 5 rows corresponding in tableB (ordered by idB).

我有两个表:tableA(idA,titleA)和tableB(idB,idA,textB),它们之间有一对多的关系。对于tableA中的每一行,我想检索tableB中对应的最后5行(按idB排序)。

I've tried

SELECT * FROM tableA INNER JOIN tableB ON tableA.idA = tableB.idA LIMIT 5

but it's just limiting the global result of INNER JOIN whereas I want to limit the result for each different tableA.id

但它只是限制了INNER JOIN的全局结果,而我想限制每个不同tableA.id的结果

How can I do that ?

我怎样才能做到这一点 ?

Thanks

4 个解决方案

#1

I think this is what you need:

我想这就是你需要的:

SELECT tableA.idA, tableA.titleA, temp.idB, temp.textB
FROM tableA
INNER JOIN
(
    SELECT tB1.idB, tB2.idA,
    (
        SELECT textB
        FROM tableB
        WHERE tableB.idB = tB1.idB
    ) as textB
    FROM tableB as tB1
        JOIN tableB as tB2
            ON tB1.idA = tB2.idA AND tB1.idB >= tB2.idB
    GROUP BY tB1.idA, tB1.idB
    HAVING COUNT(*) <= 5
    ORDER BY idA, idB
) as temp
ON tableA.idA = temp.idA

More info about this method here:

有关此方法的更多信息:

http://www.sql-ex.ru/help/select16.php

#2

Much simplified and corrected Carlos solution (his solution would return first 5 rows, not last...):

很多简化和纠正的Carlos解决方案(他的解决方案将返回前5行,而不是最后......):

SELECT tB1.idA, tB1.idB, tB1.textB
FROM tableB as tB1
    JOIN tableB as tB2
        ON tB1.idA = tB2.idA AND tB1.idB <= tB2.idB
GROUP BY tB1.idA, tB1.idB
HAVING COUNT(*) <= 5

In MySQL, you may use tB1.textB even if it is group by query, because you are grouping by the idB in the first table, so there is only single value of tB1.textB for each group...

在MySQL中,你可以使用tB1.textB,即使它是逐个查询的,因为你是按照第一个表中的idB进行分组,所以每个组只有一个值为tB1.textB ...

#3

Ensure your "B" table has an index on ( idA, idB ) for optimized order by purposes so for each "A" ID, it can quickly have the "B" order descending thus putting the newest to the top PER EACH "A" ID. Using the MySQL variables, every time the "A" ID changes, it resets the rank back to 1 for the next "A" id.

确保您的“B”表在(idA,idB)上有一个索引,以便按目的优化顺序,因此对于每个“A”ID,它可以快速地使“B”顺序下降,从而将最新的PER到每个“A” ID。使用MySQL变量,每次“A”ID改变时,它会将等级重置为1以用于下一个“A”id。

select 
      B.idA,
      B.idB,
      B.textB
      @RankSeq := if( @LastAGroup = B.idA, @RankSeq +1, 1 ) ARankSeq,
      @LastAGroup := B.idA as ignoreIt
   from
      tableB B
         JOIN tableA A
            on B.idA = A.idA,
      (select @RankSeq := 0, @LastAGroup := 0 ) SQLVars 
   having
      ARankSeq <= 5
   order by
      B.idA,
      B.idB DESC

#4

select * from tablea ta, tableb tb
where ta.ida=tb.idb and tb.idb in 
(select top 5 idb from tableB order by idb asc/desc)

(asc if you want lower ids desc if you want higher ids)

(asc如果你想要更低的ID,如果你想要更高的id)

less complicated and easy to include more conditions

不太复杂,容易包含更多条件

if top clause is not present in mysql use limit clause (I don't have much knowledge abt mysql)

如果mysql使用限制子句中没有top子句(我没有太多的知识abt mysql)

#1