如何在postgresql中使用LIMIT和OFFSET来连接一对多的表？

Question

如何在postgresql中使用LIMIT和OFFSET来连接一对多的表？

sqlpostgresqlsql-order-bywindow-functionssql-limit

4

我正在使用SQLAlchemy核心库与Postgres数据库，并实现JSON:API规范。

对于分页，我只是使用基本的LIMIT和OFFSET。因此，例如对于以下两个表：

session
id
name

appearance
id
date
session_id

对于会话，我可以简单地进行分页，如下：

SELECT id, name FROM session LIMIT 20 OFFSET 40

由于数据保留时间的限制，我们的数据库只能增长到一定程度，因此我不担心LIMIT OFFSET 方法可能会导致的减速。在上面的例子中，这种方法很好用，记录数始终等于限制数量，或者在末尾时更少。但是当我有以下情况时，问题就来了：

SELECT s.id, s.name, a.date FROM session s JOIN appearance a on s.id = a.session_id LIMIT 3 OFFSET 0

由于可能会有两个引用会话的外观行，因此最终可能会出现以下情况：

s.id     s.name                 a.date
1        FirstSesh              24/04/14
1        FirstSesh              01/01/20
2        Hello                  09/09/10

现在我只返回了一行，可能还有另一行或者只是另一行的一部分。

我想到的第一个解决方案是：

SELECT s.id, s.name, a.date FROM (SELECT id, name FROM session LIMIT 3 OFFSET 0) s JOIN appearance a on s.id = a.session_id

但是现在我的能力被限制在使用s上的 WHERE或ORDER，因为它将被限制为三个。我不能只把所有这些条件放在子查询中，因为我已经建立了JSON:API关系部分的方式，并且因为我想限制是否基于例如出现日期早于2012年来返回session所以会导致相同的问题。参考每个类型在我的JSON:API设置中都有自己的查询，然后当在关系中使用时，这些查询被用作子查询，这允许简单的递归关系和新关系的简单实现。如果我可以像使用LIMIT和OFFSET那样基于会话ID的组来做些事情，那么我认为可能会起作用？但我不知道该怎么做？

- Levi H

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，

- GMB · Accepted Answer

您可以使用窗口函数。例如，这将为您提供前3个会话（按id排序），以及所有相应的出现次数（无论有多少匹配项）。

SELECT s.id, s.name, a.date 
FROM (SELECT s.*, ROW_NUMBER() OVER(ORDER BY id) rn FROM sessions) s 
INNER JOIN appearance a ON s.id = a.session_id 
WHERE s.rn BETWEEN 0 AND 3
ORDER BY s.rn, a.date

您可以通过更改BETWEEN条件的范围来“分页”结果集。

编辑

或者：

SELECT id, name, date
FROM (
    SELECT s.id, s.name, a.date,
        DENSE_RANK () OVER(ORDER BY id) rn
    FROM sessions s 
    INNER JOIN appearance a ON s.id = a.session_id 
    WHERE a.is_admin = 1
) c
WHERE s.rn BETWEEN 0 AND 3
ORDER BY rn, date