如何按insert sequence DESC排序数据?

auto_increment 列对记录进行编号。然后您可以按该列排序。

MySQL docs on auto_increment

听起来像是要使用自定义排序顺序排序。想到两种方法:1)将排序顺序放入表中,并在查询中使用该表;2) 使用带分隔符的字符串(假设域中的值的数目较小、稳定,并且值的宽度相似且“窄”)。

请原谅SQL Server的语法,但相当标准,希望您能大致了解:

WITH MyTable (ID, data_col)
     AS
     (
      SELECT ID, data_col
        FROM (
              VALUES (1, 'aa'), 
                     (2, '111'), 
                     (3, 'ccc'), 
                     (4, 'bbb'), 
                     (5, '111'), 
                     (6, 'aa'), 
                     (7, '111'), 
                     (8, 'bbb')
             ) AS MyTable (ID, data_col)
     ), 
     MyDomainWithSortOrder (domain_col, sort_seq)
     AS
     (
      SELECT ID, domain_col
        FROM (
              VALUES ('bbb', 1), 
                     ('111', 2), 
                     ('aa', 3), 
                     ('ccc', 4)
             ) AS MyDomainWithSortOrder (ID, domain_col)
     )
SELECT T1.ID, T1.data_col, D1.sort_seq
  FROM MyTable AS T1
       INNER JOIN MyDomainWithSortOrder AS D1
          ON T1.data_col = D1.domain_col
 ORDER 
    BY sort_seq;

e、 g.2级

WITH MyTable (ID, data_col)
     AS
     (
      SELECT ID, data_col
        FROM (
              VALUES (1, 'aa'), 
                     (2, '111'), 
                     (3, 'ccc'), 
                     (4, 'bbb'), 
                     (5, '111'), 
                     (6, 'aa'), 
                     (7, '111'), 
                     (8, 'bbb')
             ) AS MyTable (ID, data_col)
     )
SELECT ID, data_col
  FROM MyTable
 ORDER 
    BY CHARINDEX(CAST(data_col + ' ' AS CHAR(3)), 'bbb111aa ccc');

你喜欢这个工作吗?

SELECT @rownum := @rownum +1 `rank`,
       T.*
  FROM TestTable T,
       ( SELECT @rownum :=0 ) T
 ORDER BY `rank` DESC