如何在Sql Server中为主键指定自定义排序顺序

Question

我将列类型为varchar的表作为主键；主键也是聚集索引。主键的值是前缀I或X，后跟递增的数字(I1、I2、I3、...、X1、X2、X3等)。当前的默认排序方式为I1、I11、I111、I2、I21、I22、...

有没有办法在创建key并插入到树中时，按数字顺序指定我的主键列的排序顺序；而不是在select查询时间？因此，排序顺序为I1、I2、I3、...、I11、I12、I13

谢谢

Answer 1

您可以尝试仅使用列的数字部分进行排序，并转换为整数：

SELECT *
FROM yourTable
ORDER BY CAST(SUBSTRING(col, 2, LEN(col)-1) AS INT);

这将按如下方式排序：

I1
X1
I11
X11

如果还想将I字段与X字段分开，可以向ORDER BY子句添加另一个级别：

ORDER BY
    LEFT(col, 1),
    CAST(SUBSTRING(col, 2, LEN(col)-1) AS INT);

这里真正的问题是，您在单个列中存储文本和数字(作为文本)。最好有一个numerical ID列，以及一个单独的文本列。

Answer 2

如果你想先用'I‘对前缀进行排序，然后用'X’作为前缀，那么试试这个。

例如：I1，I2，I3，...，X1，X2，X3，...

Select * from Table
order by Cast(REPLACE(REPLACE(PKColumn, 'I', '1'), 'X', '2') as int)

样本数据

I1, I11, I2, I3, I111, I2, I21, I22, ..., X1, X2, X3

结果顺序

I1, I2, I2, I3, I11, I21, I22, I111,  ..., X1, X2, X3

但是如果你想忽略前缀而只排序...，那么试试这个。

示例：I1、X1、I2、X2、I3、X3 ...

Select * from ORDERS
order by Cast(RIGHT(VALUE, LEN(VALUE) - 1) as int);

样本数据

I1, I11, I2, I3, I111, I2, I21, I22, ..., X1, X2, X3

结果顺序

I1, X1, I2, I2, X2, I3, X3, I11, I21, I22, I111,  ...

如果我理解不正确或查询不起作用，请让我知道。