mysql - 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 将第二个 block 转换为数字:

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 形式的前缀，那么您可以简化 ORDER BY:

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