排序Varchar2表ID

Question

我正在尝试以正确的数字顺序对Location_ID进行排序。我为Location_ID使用了Varchar2数据类型，创建了一个SELECT语句来检索所有疾病及其总数。目前，输出如下所示。我想让L20成为最后的结果。

Location_ID    Name    Outlier_Value   Total
---------------------------------------------
L1            Disease      < 1         1
L2            Disease      < 1         1                 
L20           Disease      < 1         1
L3            Disease      < 1         1
L4            Disease      < 1         1
L5            Disease      < 1         1


SELECT Location_ID, Name, Outlier_Value, COUNT(*) AS 
Total 
FROM Measurement 
WHERE Outlier_Value IS NOT NULL 
GROUP BY Location_ID, Name, Outlier_Value
ORDER BY Name ASC;

Answer 1

order by to_number(regexp_substr(location_id, '\d+$'))

可能是一种选择。

Answer 2

假设您的位置以单个字母开头，那么您可以先按长度排序，然后按值排序：

order by length(location_id), location_id

Answer 3

您还可以使用适当的leftpad设置字符串的格式

order by lpad(location_id, 10,'0')