php - 将嵌套行与动态 ORDER BY 和 LIMIT/分页保持在一起

Question

考虑需要像这样显示数据的情况:

Parent 1 info
    Child 1A info
    Child 1B info
    ...
Parent 2 info
    Child 2A info
    Child 2B info
    ...

请注意，子级只深入一层，因此无需进入数据库级别的“嵌套集”。比如我现在的项目中，数据需要这样显示:

First name  | Last name | City  | ...
---------------------------------
John        | Doe       | Denver

    Sales:
    ---------------------
    Date    | Total | ...
    -----------------
    Jan 1   | $100
    Jan 15  | $200
    Feb 1   | $100

Suzie       | Springer  | New York

    Sales:
    ---------------------
    Date    | Total | ...
    -----------------
    ...

我的问题是关于在通过使用 LIMIT 子句(或等效子句)进行分页的同时获取此页面的数据所需的 SQL。

在这种情况下，应首先列出具有最近销售额的帐户，但当然每个帐户都需要包含其下的所有销售额，直到一年前。

让我先问一个更简单的情况:假设排序顺序无关紧要。然后我可以简单地按帐户 ID 订购(简化示例):

SELECT sh.account_id, a.first_name, a.last_name,
    sh.sale_id, sh.total, sh.payment_time
FROM sales_header sh
JOIN account a on sh.account_id = a.account_id
WHERE a.account_type = 'parent'
ORDER BY sh.account_id

但是当引入分页时，如何确保将特定帐户的所有销售额都放在一起？很难知道 LIMIT 应该有多大。

现在让我们添加所需的实际排序顺序:

SELECT sh.account_id, a.first_name, a.last_name,
    sh.sale_id, sh.total, sh.payment_time
FROM sales_header sh
JOIN account a on sh.account_id = a.account_id
WHERE a.account_type = 'parent'
ORDER BY sh.payment_time

当检索所有行时，没有问题——我可以简单地运行查询，然后在我的应用程序代码(在本例中为 PHP)中通过 account_id 为结果编制索引。

但如果我添加一个 LIMIT，那么我可能无法获得每个账户的所有销售额，尤其是对于同时具有旧销售额和近期销售额的账户。

我已经想出了一个解决方案，但它相当复杂，特别是因为我处理的是动态查询参数，而不是我在这里展示的简化版本。我的解决方案是运行两个单独的查询。

首先，获取我需要的所有帐户 ID:

SELECT DISTINCT sh.account_id
FROM sales_header sh
JOIN account a on sh.account_id = a.account_id
WHERE a.account_type = 'parent'
ORDER BY sh.payment_time
LIMIT 10

然后，将 ID 放入名为 $account_ids 的 PHP 数组中，然后构建返回显示页面所需的实际结果的查询:

$sql = 'SELECT sh.account_id, a.first_name, a.last_name,
    sh.sale_id, sh.total, sh.payment_time
FROM sales_header sh
JOIN account a on sh.account_id = a.account_id
WHERE sh.account_id IN ('. implode(',', $account_ids). ')
ORDER BY sh.payment_time';

当然，这也可以在单个查询中完成，方法是将首先查询 implode() 函数在哪里，比如:

WHERE sh.account_id IN (SELECT DISTINCT sh.account_id ... LIMIT 10)

但这引起了对子查询和数据库可移植性限制的担忧 -我使用的框架允许您使用可移植的 limit() 方法设置限制到不同的数据库，但我不确定如果我尝试在子查询中使用限制它会如何 react 例如，SQL Server 就是这样。

所以，我真正的问题是:这是解决问题的最佳方法，还是有替代(更简单)的解决方案？你在类似情况下做了什么？

Answer 1

SELECT
    sh.account_id, a.first_name, a.last_name,
    sh.sale_id, sh.total, sh.payment_time
FROM
    sales_header sh
    inner JOIN
    account a on sh.account_id = a.account_id
    inner join (
        SELECT sh.account_id, max(sh.payment_time) payment_time
        FROM
            sales_header sh
            inner JOIN
            account a on sh.account_id = a.account_id
        WHERE a.account_type = 'parent'
        group by sh.account_id
        order by 2 desc
        limit 10
    ) acs on acs.account_id = a.account_id
ORDER BY acs.payment_time desc, acs.account_id, sh.payment_time desc

我试图让它与数据库无关，但 limit 子句必须在 Sql Server 中转换为 select top 10。