如何为mysql中显示的内容显示升序或降序

Question

现在显示的输出：

1234/45/67-9
1234/45/67-8
1234/45/67-7
1234/45/67-6
1234/45/67-5
1234/45/67-4
1234/45/67-3
1234/45/67-22
1234/45/67-2
1234/45/67-10
1234/45/67-1
1234/45/67

所需输出：

1234/45/67-22
1234/45/67-10
1234/45/67-9
1234/45/67-8
1234/45/67-7
1234/45/67-6
1234/45/67-5
1234/45/67-4
1234/45/67-3
1234/45/67-2
1234/45/67-1
1234/45/67

SELECT invoiceNo
FROM invoice
WHERE invoiceNo LIKE '1234/45/67%'
ORDER BY invoiceNo DESC

我希望输出以降序显示，但它不能以正确的方式显示？如何实现？

Answer 1

如果前缀总是10个字符长，则可以使用substring将其切碎，并将第二个块转换为具有cast:的数字

select invoiceno
from invoice
where invoiceno like '1234/45/67%'
order by substring(invoiceno from 1 for 10),
         cast(substring(invoiceno from 11) as decimal);

将第二部分转换为数字可以使它们像数字而不是字符串一样排序，因此-10位于-1之前，而不是相反。如果您总是在WHERE子句中使用形式为9999/99/99的前缀，那么您可以简化ORDERBY:

select invoiceno
from invoice
where invoiceno like '1234/45/67%'
order by cast(substring(invoiceno from 11) as decimal);

Answer 2

使用辅助计算字段强制转换为integer，如下所示：

SELECT invoiceNo, CAST(SUBSTR(invoiceNo FROM 11) AS INT) AS invoiceNumber
FROM invoice
WHERE invoiceNo LIKE '1234/45/67%'
ORDER BY invoiceNumber DESC

Answer 3

尝试此查询-

SELECT * FROM (
  SELECT invoiceNo,
    @third_part:=SUBSTRING_INDEX(invoiceno, '/', -1) tp
  FROM
    invoice
  WHERE
    invoiceNo LIKE '1234/45/67%'
  ) t
ORDER BY
  SUBSTRING_INDEX(tp, '-', 1) * 1 DESC,
  IF (LOCATE('-', tp) = 0, 0, SUBSTRING_INDEX(tp, '-', -1) * 1) DESC

Answer 4

尝试此查询

SELECT invoiceNo
FROM invoice
ORDER BY cast(substring(invoiceNo from 11) as decimal) ASC