php - 如何以这种方式对数组进行排序(如果它包含 A B 那么它将首先出现，然后在查询给出结果的顺序之后)

Question

<?php
      require 'connection.php';
      $sql1="SELECT m.allocationID,mt.CatId,mt.CatSName FROM msttransaction 
      m,msttemp mt WHERE m.isPending='Y' AND m.allocationID IN ( SELECT 
      mt.AllocationId FROM msttemp WHERE mt.quarterId='020500001') order by 
      SUBSTRING(m.AllocationId, -14)";
      $query=mysqli_query($con,$sql1);
      $rows=mysqli_num_rows($query);
      for ($i = 0; $i<$rows; $i++) 
      {
          $result=mysqli_fetch_array($query);
          $a=$result['allocationID'];
          $b=$result['CatId'];
          $c=$result['CatSName'];
          echo $a.'  '.$b.'  '.$c."<br>";
      }
?>

输出:

12980013420170919125529 4 D
12980013720170919125816 1 A
12980013820170919130050 2 B
12980013920170919130209 3 C
12980014020170919130434 4 D
12980014120170920140935 1 A

Note:-129800 series having last 14 digits date and time

我想将此数组排列为

12980013720170919125816 1 A
12980014120170920140935 1 A
12980013820170919130050 2 B
12980013420170919125529 4 D
12980013920170919130209 3 C
12980014020170919130434 4 D

所以每次如果数组包含A和B，它首先排序(先A然后B)然后其他元素保持相同的顺序(或者我可以根据日期和时间说)..

Answer 1

实际上，如果我理解你的意思，你必须定义CASE语句:

$sql1="SELECT 
           m.allocationID,
           mt.CatId,
           mt.CatSName,
           CASE 
               WHEN mt.CatSName = 'A' THEN 0
               WHEN mt.CatSName = 'B' THEN 1
               ELSE 2
           END CASE as ord
       FROM 
           msttransaction m,
           msttemp mt 
       WHERE 
           m.isPending='Y' AND m.allocationID 
       IN ( SELECT 
                mt.AllocationId 
            FROM 
                msttemp 
            WHERE 
                mt.quarterId='020500001') 
        ORDER BY
            ord,
            CatId,
            SUBSTRING(m.AllocationId, -14)";

在性能方面，使用联合也会比在 FROM 语句中调用拖表更好