T-Sql - 按字母数字排序

Question

我有一个字母数字标记列表，例如 '1a'、'1b'、'02'、'03'、'10'、'11'、 等...

现在，最好的方法是什么在此令牌列表上进行排序？

我收到 '1a', '1b', '10', '11', '02', '03',

但我需要它

'1a', '1b', '02', '03', '10', '11' 

更新

ok，我在建议后这样做，但它不起作用。

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
select '1b'
select '02'
select '10'

select * from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

我收到的响应为 '1b', '02', '10', '1a'

UPDATE2

它在进行以下更改后有效。

declare @tokens table(token varchar(20));

insert into @tokens
select '1a'
insert into @tokens
select '1b'
insert into @tokens
select '02'
insert into @tokens
select '10'


select token from @tokens
order by case
 when ISNUMERIC(token) = 1 then right('0000000000'+token+'0',10)
 else right('0000000000'+token,10)
 end

感谢大家的好主意。

Answer 1

最简单的解决方案是预先添加零，

Select ...
From Table
Order By Right( '0000000000' + YourColumn, 10)

但是，这不会考虑字母字符。为了处理字母字符，您需要知道可能有多少个潜在的字母字符。如果有的话，您可以执行以下操作：

Select ...
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

添加

测试运行：

If object_id('tempdb..#Test') is not null
    Drop Table #Test

Create Table #Test ( NumVal varchar(10) )
Insert #Test(NumVal) Values('02')
Insert #Test(NumVal) Values('03')
Insert #Test(NumVal) Values('1a')
Insert #Test(NumVal) Values('1b')
Insert #Test(NumVal) Values('10')
Insert #Test(NumVal) Values('11')

Select NumVal
From #Test
Order By Case
    When IsNumeric(NumVal) = 1 Then Right('0000000000' + NumVal + '0', 10)
    Else Right('0000000000' + NumVal, 10)
    End

Results:
1a
1b
02
03
10
11

关于我的解决方案的注释。如果字母字符具有特殊含义，那么正如 Erick Robertson 建议的那样，您应该将数据分解为单独的列。上述解决方案仅处理两种非常特殊的情况：全数字值、带有单个尾随字母字符的值。如果数据可能包含多个字母字符，或者字母字符有时位于值末尾以外的位置，则我的解决方案将不起作用。此外，应该注意的是，我的解决方案将导致表扫描来评估每个值的可排序字符串。

如果您寻求的是一次性快速解决方案，那么我的方法将会起作用。如果您正在寻求长期解决方案，那么要么将数据分解为单独的列，接受愚蠢的排序顺序，要么添加一个列来规定每个值的相对排序顺序。

Answer 2

如果您熟悉 C# 或 VB.net，可能值得考虑编写一个 CLR 函数来为您执行排序，因为这种排序顺序非常不标准，很难在 TSQL 中全面、正确地描述。

Answer 3

最好的解决方案是有一个单独的字段来存储令牌的 int 值。当您维护 token 列时，您应该维护此列。然后，当您排序时，先按 int 值列排序，然后按 token 列排序。这将允许您为这些列建立索引，以便快速检索大型数据集的数据。

从 alpha 到 int 的转换函数速度很慢，并且无法利用索引来加速查询。随着数据集的增长，这种类型的解决方案只会变得更慢，并使数据库陷入困境。