php - 我想在类别中按日期从早期到当前排列我的记录

Question

查询我在代码中使用的-

SET @a:=-1;

SET @b:=0;

SELECT m.allocationid,
       mt.catid,
       mt.catsname,
       r.officername,
       r.desgname,
       r.grade,
       r.cellno,
       IF(mt.catsname = 'E', @a := @a + 2,
       IF(mt.catsname = 'D', 0, @b := @b + 2)) AS
       rwid
FROM   msttransaction m,
       msttemp mt,
       regusers r
WHERE  ( m.ispending = 'Y'
         AND m.statusr = 'Approve'
         AND m.iscancelled = 'No'
         AND m.allocationid IN (SELECT mt.allocationid
                                FROM   msttemp
                                WHERE  mt.quarterid = '082505012')
         AND Substring(m.allocationid, 1, 11) = r.pfnpsno )
        OR ( m.ispending = 'N'
             AND m.type = 'Transit'
             AND m.statusr = 'Approve'
             AND m.iscancelled = 'No'
             AND m.allocationid IN (SELECT mt.allocationid
                                    FROM   msttemp
                                    WHERE  mt.quarterid = '082505012')
             AND Substring(m.allocationid, 1, 11) = r.pfnpsno )
ORDER  BY rwid,
          Substring(m.allocationid, 14) 

此查询显示输出为

SUBSTRING(m.AllocationId,-14) CatID  CatSName OfficerName        rwid
20160812000000                  4     D       TARUN DANDOTIYA     0
20160911114106                  4     D       Rahul N Chauhan     0
20180601000000                  5     E       ARUN  KUMAR         7
20170403000000                  6     F       VEERSINGH MEENA     2
20160503000000                  5     E       HEMANT  JINDAL      1
20170529000000                  6     F       AJAY RAJ            6
20161227000000                  5     E       G M SRINIVASAN      3
20170526000000                  6     F       S M KELKAR          4
20180621000000                  5     E       JANYU  JINESH       9
20170704000000                  6     F       AJAY SINGH          8
20180504000000                  5     E       GANESH  LONARE      5
20170914000000                  6     F       UMANG DUBEY         10

但我希望我的结果为

SUBSTRING(m.AllocationId,-14) CatID  CatSName OfficerName        rwid
20160911114106                  4     D       Rahul N Chauhan     0
20160812000000                  4     D       TARUN DANDOTIYA     0
20160503000000                  5     E       HEMANT  JINDAL      1
20170403000000                  6     F       VEERSINGH MEENA     2
20161227000000                  5     E       G M SRINIVASAN      3
20170526000000                  6     F       S M KELKAR          4
20180504000000                  5     E       GANESH  LONARE      5
20170529000000                  6     F       AJAY RAJ            6
20180601000000                  5     E       ARUN  KUMAR         7
20170704000000                  6     F       AJAY SINGH          8
20180621000000                  5     E       JANYU  JINESH       9
20170914000000                  6     F       UMANG DUBEY         10

注意 - 这里我没有在表输出中显示 AllocationId

我想根据日期安排我的输出，即 SUBSTRING(m.AllocationId, -14) 在我的情况下在类别中，这意味着 D 类别首先出现在类别中，希望你们理解，提前致谢

Answer 1

您可以按类别然后按日期订购:

ORDER  BY CatID, Substring(m.allocationid, 14)