我们有一个大型数据库，在数据库上有DB端分页。这很快，只需几秒钟即可从数百万条记录返回50行的页面。

用户可以定义自己的排序方式，基本上是选择要作为排序依据的列。列是动态的，有些具有数值，一些日期和一些文本。

尽管大多数排序都是按预期方式进行的，但文本却以愚蠢的方式排序。好吧，我说这很愚蠢，这对计算机有意义，但会让用户感到沮丧。

例如，按字符串记录ID排序可得到类似以下内容的结果：

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...等等。

我希望这个考虑到这个数字，所以：

rec1
rec2
rec3
rec4
rec10
rec14
rec20

我无法控制输入(否则我只能将格式设置为前导000)，并且我不能依赖单一格式，例如" {alpha code}-{dept code}-{rec id}"之类的东西。

我知道几种在C＃中执行此操作的方法，但是无法拉下所有记录来对它们进行排序，因为那样会很慢。

有谁知道一种在SQL Server中快速应用自然排序的方法？

我们正在使用：

ROW_NUMBER() over (order by {field name} asc)

然后我们按此进行分页。

我们可以添加触发器，尽管不能。他们所有的输入都是经过参数设置的，但如果将它们放入" rec2"和" rec10"中，我希望它们以自然顺序返回，就不能更改格式。

我们有有效的用户输入，这些输入针对不同的客户端采用不同的格式。

可能会进入rec1，rec2，rec3，... rec100，rec101

可能还有另一个：grp1rec1，grp1rec2，... grp20rec300，grp20rec301

当我说我们无法控制输入时，我的意思是我们不能强迫用户更改这些标准，它们的值类似于grp1rec1，而我不能将其重新格式化为grp01rec001，因为那将更改用于查找和链接的内容外部系统。

这些格式差异很大，但通常是字母和数字的混合形式。

将这些以Cis easy进行排序，只需将其分解为{" grp"，20，" rec"，301}，然后依次比较序列值。

但是，可能有数百万条记录并且分页了数据，我需要在SQL Server上进行排序。

SQL Server是按值排序的，而不是CI中的比较不能将值拆分出来进行比较，但是在SQL中，我需要一些逻辑(非常迅速)获得一个始终排序的单个值的逻辑。

@moebius，答案可能有用，但是为所有这些文本值添加排序键确实感觉很难受。

解决方案

回答

我还是听不懂(可能是因为我英语不好)。

我们可以尝试：

ROW_NUMBER() OVER (ORDER BY dbo.human_sort(field_name) ASC)

但这对数百万条记录无效。

这就是为什么我建议使用触发器将人的价值填充到单独的列中的原因。

而且：

• 内置的T-SQL函数确实很慢，Microsoft建议改为使用.NET函数。
• 人为值是恒定的，因此每次查询运行时都没有必要计算它。

回答

如果我们无法从数据库中加载数据以进行C＃排序，那么我敢肯定，对于以编程方式在数据库中进行处理的任何方法，我们都会感到失望。当服务器要排序时，就必须像每次一样计算"感知"顺序。

我建议我们在首次插入数据时使用一些Cmethod添加一个添加列来存储预处理的可排序字符串。例如，我们可能尝试将数字转换为固定宽度范围，因此" xyz1"将变成" xyz00000001"。然后，我们可以使用普通的SQL Server排序。

冒着冒出自己的号角的风险，我写了一篇CodeProject文章来实现CodingHorror文章中提出的问题。随时从我的代码中窃取。

回答

我知道这有点老了，但是在寻找更好的解决方案时，我遇到了这个问题。我目前正在使用一个函数进行排序。对于我排序以混合字母数字命名的记录("第1项"，"第10项"，"第2项等")的目的，它工作得很好

CREATE FUNCTION [dbo].[fnMixSort]
(
    @ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS

BEGIN
    DECLARE @p1 NVARCHAR(255),
        @p2 NVARCHAR(255),
        @p3 NVARCHAR(255),
        @p4 NVARCHAR(255),
        @Index TINYINT

    IF @ColValue LIKE '[a-z]%'
        SELECT  @Index = PATINDEX('%[0-9]%', @ColValue),
            @p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
            @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
    ELSE
        SELECT  @p1 = REPLICATE(' ', 255)

    SELECT  @Index = PATINDEX('%[^0-9]%', @ColValue)

    IF @Index = 0
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
            @ColValue = ''
    ELSE
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    SELECT  @Index = PATINDEX('%[0-9,a-z]%', @ColValue)

    IF @Index = 0
        SELECT  @p3 = REPLICATE(' ', 255)
    ELSE
        SELECT  @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    IF PATINDEX('%[^0-9]%', @ColValue) = 0
        SELECT  @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
    ELSE
        SELECT  @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)

    RETURN  @p1 + @p2 + @p3 + @p4

END

然后打电话

select item_name from my_table order by fnMixSort(item_name)

对于简单的数据读取，它很容易将处理时间增加三倍，因此它可能不是理想的解决方案。

回答

order by LEN(value), value

并不完美，但是在很多情况下效果很好。

回答

我们可以使用以下代码解决问题：

Select *, 
    substring(Cote,1,len(Cote) - Len(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1)))alpha,
    CAST(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1) AS INT)intv 
FROM Documents 
   left outer join Sites ON Sites.IDSite = Documents.IDSite 
Order BY alpha, intv

问候，
[email protected]

回答

我刚刚在某处阅读了有关该主题的文章。关键点是：我们只需要整数值即可对数据进行排序，而'rec'字符串属于UI。我们可以将信息分成两个字段，例如alpha和num，分别按alpha和num排序，然后显示一个由alpha + num组成的字符串。我们可以使用计算列来组成字符串或者视图。
希望能帮助到你

回答

我见过的大多数基于SQL的解决方案都在数据变得足够复杂(例如其中包含一个或者两个以上的数字)时中断。最初，我尝试在T-SQL中实现满足我的要求的NaturalSort函数(除其他事项外，它在字符串中处理任意数量的数字)，但是性能太慢了。

最终，我在Cto中编写了标量CLR函数，以实现自然排序，即使使用未经优化的代码，从SQL Server调用它的性能也非常快。具有以下特点：

• 会正确排序前1,000个左右的字符(可以轻松地在代码中修改或者制成参数)
• 正确地对小数进行排序，因此123.333在123.45之前
• 由于上述原因，可能无法正确排序IP地址之类的内容；如果我们希望其他行为，请修改代码
• 支持对其中包含任意数字的字符串进行排序
• 将正确地对不超过25位数字的数字进行排序(可以轻松地在代码中修改或者制成参数)

代码在这里：

using System;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

public class UDF
{
    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic=true)]
    public static SqlString Naturalize(string val)
    {
        if (String.IsNullOrEmpty(val))
            return val;

        while(val.Contains("  "))
            val = val.Replace("  ", " ");

        const int maxLength = 1000;
        const int padLength = 25;

        bool inNumber = false;
        bool isDecimal = false;
        int numStart = 0;
        int numLength = 0;
        int length = val.Length < maxLength ? val.Length : maxLength;

        //TODO: optimize this so that we exit for loop once sb.ToString() >= maxLength
        var sb = new StringBuilder();
        for (var i = 0; i < length; i++)
        {
            int charCode = (int)val[i];
            if (charCode >= 48 && charCode <= 57)
            {
                if (!inNumber)
                {
                    numStart = i;
                    numLength = 1;
                    inNumber = true;
                    continue;
                }
                numLength++;
                continue;
            }
            if (inNumber)
            {
                sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));
                inNumber = false;
            }
            isDecimal = (charCode == 46);
            sb.Append(val[i]);
        }
        if (inNumber)
            sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));

        var ret = sb.ToString();
        if (ret.Length > maxLength)
            return ret.Substring(0, maxLength);

        return ret;
    }

    static string PadNumber(string num, bool isDecimal, int padLength)
    {
        return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0');
    }
}

若要进行注册，以便可以从SQL Server调用它，请在查询分析器中运行以下命令：

CREATE ASSEMBLY SqlServerClr FROM 'SqlServerClr.dll' --put the full path to DLL here
go
CREATE FUNCTION Naturalize(@val as nvarchar(max)) RETURNS nvarchar(1000) 
EXTERNAL NAME SqlServerClr.UDF.Naturalize
go

然后，我们可以像这样使用它：

select *
from MyTable
order by dbo.Naturalize(MyTextField)

注意：如果我们在SQL Server中遇到错误，则将禁用.NET Framework中的用户代码执行。启用" clr enabled"配置选项。，请按照此处的说明进行启用。确保这样做之前先考虑安全隐患。如果我们不是数据库管理员，请确保在与服务器配置进行任何更改之前与管理员进行讨论。

注意2：此代码不正确支持国际化(例如，假定小数点标记为"。"，未针对速度进行优化等)，欢迎提出改进建议！

编辑：将函数重命名为Naturalize而不是NaturalSort，因为它没有进行任何实际排序。

回答

我知道这是一个古老的问题，但是我碰到了这个问题，因为它没有被接受的答案。

我一直使用类似的方法：

SELECT [Column] FROM [Table]
ORDER BY RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))), 1000)

出现此问题的唯一常见时间是，如果列不会转换为VARCHAR(MAX)，或者LEN([Column])> 1000(但我们可以将1000更改为其他值)，但是我们可以根据需要使用这个粗略的想法。

而且，这比正常的ORDER BY [Column]的性能要差得多，但是它确实为我们提供了OP中要求的结果。

编辑：只是为了进一步澄清，如果我们具有十进制值(例如，具有" 1"，" 1.15"和" 1.5"(它们将按" {1，1.5，1.15}"排序)，则以上内容将不起作用。不是OP要求的内容，但可以通过以下方式轻松实现：

SELECT [Column] FROM [Table]
ORDER BY REPLACE(RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))) + REPLICATE('0', 100 - CHARINDEX('.', REVERSE(LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX))))), 1)), 1000), '.', '0')

结果：{1，1.15，1.5}

而且仍然全部都在SQL中。这不会对IP地址进行排序，因为我们现在正进入非常特定的数字组合，而不是简单的文本+数字。