php - 访问数据库 : TOP clause and nested queries + PHP Paging

Question

我正在开发一个 PHP 界面来显示来自两个不同数据库的一些结果:一个 MySQL 数据库 (.sql) 和一个 Access 数据库 (.mdb)。当我查询结果时，我让用户选择他想列出多少结果，这样我就可以通过一些 PHP 的乐趣来限制和分页它们。
这对于 MySQL DB 来说是完美的，其中 LIMIT 子句以一种简洁的方式完成了所有事情。 Access DB 是另一回事:Access 没有 LIMIT 子句，因此我必须使用 TOP 子句。太糟糕了，这个让寻呼变得很痛苦。
我在 this question 的已接受答案中找到了一个很好的例子，但我遇到了问题。首先，让我向您展示几个片段查询:

查询#1

SELECT TOP 5 *                 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %';

查询 #2

SELECT TOP 5 *                 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %'
ORDER BY `Table_1`.`Some_ID`;

查询 #1 仅按要求显示前 5 个结果。
太糟糕了，当我添加一个 ORDER BY 子句时，Query #2 返回所有满足我条件的结果。

像这样的 MySQL 查询不会发生同样的事情:

查询 #3

SELECT * 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %'
ORDER BY `Table_1`.`Some_ID`
LIMIT 0, 5;

那么，我的 Access 查询是否有问题？
为了完成我的问题，我的最终 Access 查询应该是这样的(我稍微修改了上述主题中提出的查询):

查询 #4

SELECT * 
FROM `Table_1` INNER JOIN `Table_2` 
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
WHERE EXISTS (
    SELECT TOP 5 * FROM (
        SELECT TOP 10 * 
        FROM `Table_1` INNER JOIN `Table_2` 
        ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
        AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
        WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %') 
    ORDER BY `Table_1`.`Some_ID` DESC) 
ORDER BY `Table_1`.`Some_ID` ASC;

Answer 1

我终于设法解决了我的问题。我会发布我的查询，以防其他人遇到同样的问题。

SELECT TOP 5 *
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Timestamp`
IN (
    SELECT TOP 5 `Table_1`.`Timestamp`
    FROM (
        SELECT TOP 10 *
        FROM `Table_1` INNER JOIN `Table_2`
        ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
        AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
        WHERE `Table_1`.`Some_ID` LIKE 'ID %'
        AND `Table_2`.`Timestamp` LIKE '2018/03/20 %'
        ORDER BY `Table_1`.`Timestamp` ASC)
    ORDER BY `Table_1`.`Timestamp` DESC)
ORDER BY `Table_1`.`Timestamp` ASC;

只需使用“ASC”和“DESC”来获得您愿意返回的结果。