获取每个组的前/后n条记录 [英] Get first/last n records per group by

问题描述

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

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

我尝试过

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

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

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

我该怎么做?

谢谢

推荐答案

我认为这是您需要的:

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

这篇关于获取每个组的前/后n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！