php - 从子查询结果中获取 MAX(value)

Question

我有一个查询，它连接 3 个表并返回按页码排序的结果，这样我一次只能查看一页，并通过数组查看其输出。

这是我的查询

select wo.*,
   (select count(distinct order)
    from (SELECT *
          FROM `movements` LEFT JOIN
               `classes`
               ON `movements`.`class_id` = `classes`.`class_id` LEFT JOIN
               `clients`
               ON `movements`.`class_id` = `clients`.`class_id`
          WHERE `classes`.`class_id` = '$class_id' 
         ) wo2
    where wo2.order <= wo.order
   ) as pagenum
from (SELECT *
  FROM `movements` LEFT JOIN
       `classes`
        ON `movements`.`class_id` = `classes`.`class_id` LEFT JOIN
        `clients`
       ON `movements`.`class_id` = `clients`.`class_id`
   WHERE `classes`.`class_id` = '$class_id' 
  ) wo
order by pagenum

现在我希望能够获得 MAX(pagenum)，这样我就可以告诉 PHP 当我到达查询末尾时。有没有办法做这样的事情(我已经尝试过，但不断收到sql语法错误)

SELECT MAX(`pagenum`) as `lastpage`

或者有更好的方法来实现这一点吗？

下面是该查询结果的屏幕截图。我目前有“下一个”和“上一个”链接，当我到达查询末尾(这些结果中的第 5 页)时，这些链接将用户从一个页面带到另一个页面，我希望 PHP 不再提供转到下一页的选项。所以我必须以某种方式获取每个查询的 MAX(pagenum) 值。

http://custommovement.com/help/woquery.png

Answer 1

您可以通过包含另一个相关子查询来获取 maxpage num:

select wo.*,
       (select count(distinct order)
        from (SELECT *
              FROM `movements` LEFT JOIN
                   `classes`
                   ON `movements`.`class_id` = `classes`.`class_id` LEFT JOIN
                   `clients`
                   ON `movements`.`class_id` = `clients`.`class_id`
                   WHERE `classes`.`class_id` = '$class_id'
             ) wo2
        where wo2.order <= wo.order
       ) as pagenum,
       (select count(distinct order)
        from (SELECT *
              FROM `movements` LEFT JOIN
                   `classes`
                   ON `movements`.`class_id` = `classes`.`class_id` LEFT JOIN
                   `clients`
                   ON `movements`.`class_id` = `clients`.`class_id`
                   WHERE `classes`.`class_id` = '$class_id'
             ) wo2
       ) as maxpages          
 from (SELECT *
       FROM `movements` LEFT JOIN
            `classes`
            ON `movements`.`class_id` = `classes`.`class_id` LEFT JOIN
            `clients`
            ON `movements`.`class_id` = `clients`.`class_id`
       WHERE `classes`.`class_id` = '$class_id'
      ) wo
order by pagenum