php - 结果集顺序在php中更改

Question

我有一个结果集，需要按原样显示在屏幕上。但问题是在使用 echo 命令显示结果集的每一行时，顺序正在改变。谁能说出为什么会这样并提供我有办法克服它。这是我的实际输出和打印输出。

实际结果集:

JAIKE-ILENE-WACKI-MAZIE-REGLE-SBJ-KMMU  
LVZ-HARTY-MUGZY-STW 
MAZIE-SIXIE-SBJ-KMMU  
PXT-LOUIE-GATBY-RAZER-BUZIE-JAIKE-ILENE-WACKI-MAZIE  
SWANN-GATBY-RAZER-BUZIE-JAIKE-ILENE-WACKI-MAZIE  

输出:

 JAIKE-SBJ-ILENE-KMMU-WACKI-MAZIE-REGLE
 MUGZY-STW-LVZ-HARTY
 SBJ-KMMU-MAZIE-SIXIE
 ILENE-GATBY-WACKI-RAZER-MAZIE-BUZIE-PXT-JAIKE-LOUIE
 WACKI-RAZER-MAZIE-BUZIE-JAIKE-SWANN-ILENE-GATBY  

这是我的代码

$sql3="SELECT GROUP_CONCAT(l.fix_ident SEPARATOR '-') AS fix_seq,l.airport_ident,x.star_ident,x.transition_ident,
                 x.fix_ident from corept.std_star_leg l
                 JOIN
                    (SELECT DISTINCT c.airport_ident,c.star_ident,c.transition_ident,c.fix_ident
                     FROM corept.std_star_leg c
                     INNER JOIN
                          (SELECT star_ident,transition_ident,max(sequence_num) seq,route_type
                           FROM corept.std_star_leg
                           WHERE data_supplier='J'
                           AND airport_ident='KMMU'
                           GROUP BY star_ident,
                           transition_ident)b ON c.sequence_num=b.seq
                           AND c.star_ident=b.star_ident AND c.transition_ident=b.transition_ident
                           LEFT JOIN
                               (SELECT name,trans
                                FROM skyplan_deploy.deploy_stars
                                WHERE apt='KMMU'
                                AND name!=trans) d
                                ON d.name=c.star_ident
                                AND d.trans=c.fix_ident
                                WHERE c.data_supplier='J'
                                AND c.airport_ident='KMMU' AND d.name is null)x
                                where l.airport_ident='KMMU' and l.transition_ident=x.transition_ident
                                and l.star_ident=x.star_ident and l.data_supplier='J'
                                group by x.star_ident,x.transition_ident
                                order by l.star_ident,x.transition_ident,l.sequence_num";
  $res3=mysqli_query($mysqli,$sql3);  
if($res3)
  {
    while($newArray3=mysqli_fetch_array($res3,MYSQLI_ASSOC))
    {
    $apt=$newArray3['airport_ident'];
    $star_ident=$newArray3['star_ident'];
    $trans_ident=$newArray3['transition_ident'];
    $fix_ident=$newArray3['fix_ident'];
    $fix_seq=$newArray3['fix_seq'];
    echo $apt.",".$star_ident.",".$trans_ident.",".$fix_ident.",COREPT,".$fix_seq;
    echo "<br>";
    }
  }
 else
 {
   printf("ERROR:%s\n",mysqli_error($mysqli));
 }

Answer 1

您的查询看起来过于复杂。它似乎按sequence_num 查找分组最大std_star_leg 记录(分组在start_ident 和transition_ident)，排除那些已经有匹配的非自引用 deploy_star 的那些，然后返回再次分组的结果，所有匹配的 fix_ident 值连接成一个字符串？

如果是这样，以下大大简化的查询应该会获得相同的结果:

SELECT   GROUP_CONCAT(fix_ident SEPARATOR '-') AS fix_seq,
         airport_ident,
         star_ident,
         transition_ident
FROM     corept.std_star_leg l NATURAL JOIN (
           SELECT   star_ident, transition_ident,
                    data_supplier, airport_ident,
                    MAX(sequence_num) sequence_num
           FROM     corept.std_star_leg
           WHERE    data_supplier = 'J'
                AND airport_ident = 'KMMU'
           GROUP BY star_ident, transition_ident
         ) b
WHERE    NOT EXISTS (
           SELECT NULL
           FROM   skyplan_deploy.deploy_stars d
           WHERE  d.name != d.trans
              AND d.name  = l.star_ident
              AND d.trans = l.fix_ident
              AND d.apt   = l.airport_ident
         )
GROUP BY star_ident, transition_ident

请注意，虽然您之前在最外层的选择列表中选择了 x.fix_ident，但我省略了这一列，因为它的值将由服务器从 fix_seq 中的那些值中不确定地选择。

现在，关于您的问题(似乎与 GROUP_CONCAT() 字符串中 fix_ident 值出现的顺序有关fix_seq——虽然很难从你的问题中理解这一点)，也许你想对 GROUP_CONCAT() 函数使用 ORDER BY 参数？例如:

SELECT GROUP_CONCAT(fix_ident SEPARATOR '-' ORDER BY ...) AS fix_seq

但是，我不清楚您需要什么顺序(原始查询中唯一的 ORDER BY 子句完全多余)。