Server Order By Ascend似乎不起作用

Question

我有一个数据库，它为用户创建记录，他们可以创建这些记录的新版本。我跟踪旧记录，因此我通过在DB中添加一列(VARCHAR(20))来区分它，该列存储数据的版本。我创建了一个存储过程，该存储过程接受与用户ID匹配的行，并以一个字符串的形式对版本进行排序，其中的版本由“\”分隔。这已经有一段时间了，但是最近有一个用户创建了第十个版本，不知怎么的，这个流似乎中断了。以下是特别方案：

DECLARE @IN_vsId VARCHAR(100)

SET @IN_vsId='user1'
SELECT @Version_str = COALESCE(@Version_str + '|', '') + VSN_NO 
FROM (
SELECT TOP 100 VSN_NO
FROM table1 myTable
WHERE myTable.ID = @IN_vsId
ORDER BY myTable.VSN_NO ASC
)VERSION_TBL

PRINT @Version_str

当用户有<9个版本时，字符串将类似于1|2|3|4|5|6|7|8|9。然而，一旦创建了第十个版本，它现在看起来就像1|10|2|3|4|5|6|7|8|9了。我在这里不知所措。我的后端依赖于获取列表的长度(当我从SP返回时，我将它转换为一个列表)，并获取最后一个索引，在本例中总是9。我可以通过从列表中获取最大int来改变后端的这种情况，但是我想看看是否可以首先更改我的DB。

到目前为止，编辑的答案工作得很好，但我有一些版本，在某些类型的用户数据前面有一个'P‘。我添加了if else语句来检测该行是否有'P‘版本，然后进行了如下操作：

DECLARE @Version_str VARCHAR(100)
DECLARE @IN_vsId VARCHAR(100)
DECLARE @IN_vsType VARCHAR(100)

SET @IN_vsId='user1'

IF @IN_vsType='DRAFT'
BEGIN
    SELECT @Version_str = COALESCE(@Version_str + '|', '') + VSN_NO 
    FROM (
    SELECT TOP 100 VSN_NO
    FROM table1 myTable
    WHERE myTable.ID = @IN_vsId
    AND myTable.TYPE_NM = @IN_vsType
    )VERSION_TBL ORDER BY TRY_CAST(VSN_NO AS INT) ASC
END
IF @IN_vsType='PUBLISH'
BEGIN
SELECT @Version_str = COALESCE(@Version_str + '|', '') + VSN_NO 
    FROM (
    SELECT TOP 100 VSN_NO
    FROM table1 myTable
    WHERE myTable.ID = @IN_vsId
    AND myTable.TYPE_NM = @IN_vsType
    )VERSION_TBL ORDER BY TRY_CAST(SUBSTRING(VSN_NO, 2, LEN(VSN_NO)) AS INT) ASC 
END

PRINT @Version_str

在P的情况下，我创建一个子字符串，移除P，并在排序时将数字转换为int

Answer 1

在SQL中，表表示无序的行集。您需要一个order by子句来排序您的数据。不重要的是，您确实命令了内部查询的结果。从外部查询的角度来看，内部查询返回的数据集只是:一组无序的行。您需要在外部查询中进行排序。

另一个问题是，您似乎将数字存储为字符串。字符串排序规则与数值数据类型的规则不一样(通常，就字符串而言，'2大于'10')。可以在排序之前转换为数字数据类型：

SET @IN_vsId='user1'
SELECT @Version_str = COALESCE(@Version_str + '|', '') + VSN_NO 
FROM (
    SELECT TOP 100 VSN_NO
    FROM table1 myTable
    WHERE myTable.ID = @IN_vsId
    ORDER BY CAST(VSN_NO AS INT) ASC
) VERSION_TBL
ORDER BY CAST(VSN_NO AS INT) ASC

如果存在某些值无法转换为数字的风险，则可以使用：

ORDER BY TRY_CAST(VSN_NO AS INT) ASC

Answer 2

问题是版本是varchar，而不是某种类型的数字。在这种情况下，值被添加为'1‘、'2’、'10‘等等。

在排序字符串时，首先比较第一个字符，然后比较第二个字符，等等。因此，如果按字母顺序排序，这是正确的：

1
10
2
20

您需要这些值要么为零填充：

01
02
10
20

或使用数字字段(int、数值等)，如果希望它们正确排序的话。

现在，考虑到上面的新信息(一些代码以字母开头)，任务变得更加复杂，但不是无法管理的。为ORDER语句尝试这样做(在数字之前只假设一个alpha字符，不要假设某些东西不能同时具有alpha和数字代码)：

ORDER BY CASE
    When IsNumeric(VSN_NO) = 1
        THEN RIGHT('000000' + VSN_NO, 6) -- assumes a max of six characters
    ELSE Left(VSN_NO, 1) + RIGHT('00000' + SUBSTRING(VSN_NO, 2, LEN(VSN_NO)), 5)
END ASC