显示来自mysql查询的部分结果

Question

我有一个sql查询，它从3个不同的表中抽取所有类型的数据，我现在想在数组中显示这些数据。我可以从sql查询中输出所有数据，但我只想根据表中列的值是否相同来一次显示数组的一部分。下面将尝试演示。

我的SQL结果看起来是这样的：

Client | Order | Exc1 | Exc2 | Rest | Reps | 
------------------------------------------------------- 
Steve | 1A  | this | That | This | that | 
------------------------------------------------------- 
Mike | 1A  | this | That | This | that | 
------------------------------------------------------- 
Jax  | 1A  | this | That | This | that | 
------------------------------------------------------- 
Steve | 1B  | this | That | This | that | 
------------------------------------------------------- 
Mike | 1B  | this | That | This | that | 
------------------------------------------------------- 
Jax  | 1B  | this | That | This | that | 

我希望我的阵列来输出这在第1页

Steve | 1A  | this | That | This | that | 
------------------------------------------------------- 
Mike | 1A  | this | That | This | that | 
------------------------------------------------------- 
Jax  | 1A  | this | That | This | that | 

这2页等上。每个页面将包含所有客户的列表，每个页面只有一个订单值。每个用户的订单栏值会有所不同，因为他们可以为订单字段输入自己的文本。这是可能的，如果是这样，最好的方式是怎样的？

@Gordon Linoff这里是我目前的查询。我真的不明白你写了什么或更多的与我的查询有关。在我目前的查询ID下面。我没有完成输出到数组中。我在测试之前就拥有了这个功能，直到我获得了我想要的结果。

function get_workout_class(){ 
$workout_class = array(); 

$workout_query = mysql_query(" 
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' AND `user_id` = ".$_session['user_id']." 
ORDER BY `movements`.`order`, `clients`.`first_name` 

"); 

}

这里是我的查询结果的屏幕截图，所以你可以看到我是多么希望他们分组。页面按不同的“顺序”值拆分。客户数量因班级而异。

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

这是我的新的查询。第一部分工作正常，但子查询给我的问题。它不拉任何结果。 @GordonLinoff

function get_workout_class($class_id){ 
$class_id = (int)$class_id; 

$workout_class = array(); 

$workout_query = mysql_query(" 
WITH `workouts` as (
SELECT 
`movements`.`movement_id`, 
`movements`.`order`, 
`movements`.`mv_00`, 
`movements`.`mv_01`, 
`movements`.`mv_02`, 
`movements`.`mv_03`, 
`movements`.`mv_04`, 
`movements`.`rep_set_sec`, 
`movements`.`rest`, 
`classes`.`class_name`, 
`clients`.`client_id`, 
`clients`.`first_name`, 
`clients`.`last_name`, 
`clients`.`nickname` 
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' 
) 

SELECT `wo`.* 
(SELECT COUNT(DISTINCT `order`) FROM `workouts` `wo2` WHERE `wo2`.`order` <= `wo`.`order`) as `pagenum` 
FROM `workouts` `wo` 
ORDER BY `pagenum` 

"); 

echo mysql_num_rows($workout_query); 
} 

Answer 1

我想你可以通过添加页码查询解决您的问题。不幸的是，这在mysql中是非常痛苦的，因为你必须使用自加入或相关的子查询。

下面是如何与相关子查询做到这一点的例子：

select t.*, 
     (select count(distinct order) from t t2 where t2.order <= t.order) as pagenum 
from t 
order by pagenum 

根据您的原始查询（但不把它变成一个字符串）：

with workouts as (
    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' AND `user_id` = ".$_session['user_id']." 
    ) 
select wo.*, 
     (select count(distinct order) from workouts wo2 where wo2.order <= wo.order) as pagenum 
from workouts wo 
order by pagenum 

这几乎工作。 。 。只是一个警告。您可以将“SELECT *”放入with子句中，因为您有多个具有相同名称的列。放入你需要的列。

我忘记了mysql不支持“with”语句。我对不好的语法表示歉意。解决办法之一是使用视图或临时表。否则，查询有点复杂，因为逻辑必须重复两次：

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' AND `user_id` = ".$_session['user_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' AND `user_id` = ".$_session['user_id']." 
    ) wo 
order by pagenum 

Answer 2

试试这个。它使用子查询作为订单的最大值。

SELECT a.* 
FROM myTable a INNER JOIN 
      (
       SELECT Client, 
         Max(`Order`) as MaxOrder 
       FROM myTable 
       GROUP BY Client 
      ) c 
       ON a.Client = c.Client AND 
        a.`Order` = c.MaxOrder 

或simpliest如果我没有记错

SELECT * 
FROM myTable 
WHERE `Order` = '1B'