如何对带有连接的 SQL 查询结果应用分页？

我有一个连接 3 个表的 SQL 查询，其中一个只是连接另外两个表的多对多。我使用 Spring JDBC ResultSetExtractor 将 ResultSet 转换为我的对象，大致如下所示：

class Customer {
    private String id;
    private Set<AccountType> accountTypes;
    ...
}

ResultSetExtractor 实现如下所示：

public List<Client> extractData(ResultSet rs) throws SQLException,
    DataAccessException {
        Map<Integer, Client> clientsMap = new LinkedHashMap<Integer, Client>();
        while (rs.next()) {
            int id = rs.getInt("id");
            // add the client to the map only the first time
            if (!clientsMap.containsKey(id)) {
                Client client = new Client();
                client.setId(id);
                ...
                clientsMap.put(id, client);
            }
            // always add the account type to the existing client
            Client client = clientsMap.get(id);
            client.addAccountType(extractAccountTypeFrom(rs, id));
        }
        return new ArrayList<Client>(clientsMap.values());
}

这在没有分页的情况下工作得很好。

但是，我需要对这些结果进行分页。我通常的做法是将其添加到查询中，例如：

SELECT ... ORDER BY name ASC LIMIT 10 OFFSET 30;

但是，由于此查询具有联接，因此当我限制结果数量时，我实际上限制了联接结果的数量（即，由于客户端出现的次数与他们拥有的帐户类型的数量相同，因此应用 LIMIT 而不是客户数量，但客户数量*accountTypes，这不是我想要的）。

我想出的唯一解决方案是从查询中删除 LIMIT （和 OFFSET，因为这也是错误的）并以编程方式应用它们：

List<Client> allClients = jdbcTemplate.query....
List<Client> result = allClients.subList(offset, offset+limit);

但这显然不是一个非常好的、有效的解决方案。有没有更好的办法？

有趣的是，写下一个问题可以让你思考，并且实际上对你自己的问题想象解决方案有很大帮助。

我只需将查询的分页部分添加到主查询的子查询中，而不是添加到主查询本身，就可以解决这个问题。

例如，不要这样做：

SELECT client.id, client.name ...
FROM clients AS client
LEFT JOIN client_account_types AS cat ON client.id = cat.client_id
FULL JOIN account_types AS at ON cat.account_type_id = at.id
ORDER BY client.name ASC
LIMIT 10 OFFSET 30;

我正在这样做：

SELECT client.id, client.name ...
FROM (
    SELECT * FROM clients
    ORDER BY name ASC
    LIMIT 10 OFFSET 0
) AS client
LEFT JOIN client_account_types AS cat ON client.id = cat.client_id
FULL JOIN account_types AS at ON cat.account_type_id = at.id;

希望这对其他人也有帮助。