按LPAD问题订购

Question

目前，我正在尝试从表中获取一个值列表，并按字母数字顺序排列，以便它们从数字到字母显示。例如，我有这个数据集

3
8
56
70
90
AK
CN
PP
PQ
W3
0.5
0.6
0.8
040
070
1.2
1.5
1.6
100
150
187
2.8
250
3.0
6.3
800
8mm

我希望它先打印0.5，然后打印W3。我使用Lpad来获取数据，但它显示如上图所示，没有排序。有没有一种方法可以在OracleSQL中用字母数字对这些进行排序？

(SQL语句(

SELECT * 
FROM data_table
ORDER BY LPAD(parameter_type, 10) ASC 
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

Answer 1

MySQL-尝试转换为十进制并检查大于零的值-

SELECT * 
FROM data_table
ORDER BY IF(
CONVERT(parameter_type, DECIMAL(6, 2)) > 0,
CONVERT(parameter_type, DECIMAL(6, 2)),
99999
) ASC, parameter_type ASC;

Oracle-如果您正在使用Oracle，则需要使用CASE，因为它没有If((函数。此外，您不能使用CAST，因为它不像MySQL那样宽容。

SELECT * 
FROM data_table
ORDER BY
CASE WHEN REGEXP_INSTR(parameter_type, '^[0-9]') > 0 THEN TO_NUMBER(REGEXP_REPLACE(parameter_type, '[^0-9.]', '')) END ASC, parameter_type ASC

这将检查第一个字符是否为[0-9]，如果是，则删除除[0-9.]之外的所有字符并将其转换为数字。db<gt；小提琴

Answer 2

MySQL

也许是这样的：

SELECT val,
val REGEXP '^[a-zA-Z]',
CAST(val AS DECIMAL(14,4))
FROM mytesting 
ORDER BY CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END ASC,
CAST(val AS DECIMAL(5,2)) ASC;

首先，我使用CASE表达式REGEXP来检查前导值是字母表还是数字；如果值以字母表开头，我将其分配给1，如果不是，我将将其分配到0，因此它将处于升序的顶部。然后添加第二个顺序，使用CAST将val数据类型更改为十进制。我把这两个操作都放在SELECT中，看看它在过滤后返回的值是多少。由于它们在SELECT中的用途仅用于查看，因此您可以将它们从最终查询中删除，这样就可以了：

SELECT val
FROM mytesting 
ORDER BY CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END ASC,
CAST(val AS DECIMAL(5,2)) ASC;

或者，如果您发现这些值很有用并且想要使用它们，您可以将查询简化为以下内容：

SELECT val,
CASE WHEN val REGEXP '^[a-zA-Z]' >= 1 THEN 1 ELSE 0 END AS val_check,
CAST(val AS DECIMAL(14,4)) AS val_convert
FROM mytesting 
ORDER BY val_check ASC,
val_convert ASC;

演示小提琴

好吧，正如你可能已经猜到的，上面的答案是针对MySQL的，并在标签更改之前发布。不幸的是，Oracle不是我的日常数据库，但本着不试图发布错误答案的精神，我这样做了：

SELECT val,
CASE WHEN REGEXP_LIKE(val,'^[0-9]')
THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
THEN CAST(val AS DEC(5,2)) 
ELSE 9000*9000 END AS val_check
FROM mytesting 
ORDER BY CASE WHEN REGEXP_LIKE(val,'^[0-9]')
THEN CAST(REGEXP_REPLACE(val,'[a-zA-Z]','')+0 AS DEC(5,2)) 
WHEN REGEXP_REPLACE(val,'[^a-zA-Z]','') IS NULL
THEN CAST(val AS DEC(5,2)) 
ELSE 9000*9000 END,
val;

目的是尝试模仿与MySQL建议的上述解决方案相同的想法。演示小提琴