猿问
下载APP

Microsoft SQL 2005中的自然(人类字母数字)排序

Microsoft SQL 2005中的自然(人类字母数字)排序

我们有一个大型数据库,我们有数据库端分页。这很快,在几分之一秒内从数百万条记录中返回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,因为这将改变用于查找和链接到外部系统。

这些格式变化很大,但通常是字母和数字的混合。

在C#中对它们进行排序很容易 - 只需将其分解{ "grp", 20, "rec", 301 },然后依次比较序列值。

但是,可能有数百万条记录并且数据被分页,我需要在SQL服务器上进行排序。

SQL服务器按值排序,而不是比较 - 在C#中我可以将值拆分为比较,但在SQL中我需要一些逻辑(非常快)获得一致排序的单个值。

@moebius - 你的答案可能会有效,但是为所有这些文本值添加排序键确实感觉像是一个丑陋的妥协。


泛舟湖上清波郎朗
浏览 42回答 3
3回答

MMTTMM

我看到的大多数基于SQL的解决方案在数据足够复杂时会中断(例如,其中包含多个或两个数字)。起初我试图实现在T-SQL一个NaturalSort功能能够满足我的要求(除其他事项外,处理数字的字符串中的任意数字),但表现方式过于缓慢。最后,我在C#中编写了一个标量CLR函数以允许自然排序,即使使用未经优化的代码,从SQL Server调用它的性能也非常快。它具有以下特点:将正确排序前1000个字符(在代码中轻松修改或作为参数)正确排序小数,所以123.333在123.45之前因为上述原因,可能无法正确排序IP地址等内容;&nbsp;如果您希望有不同的行为,请修改代码支持对其中包含任意数量的数字的字符串进行排序将正确排序最多25位数的数字(在代码中很容易修改或作为参数)代码在这里:using&nbsp;System;using&nbsp;System.Data.SqlTypes;using&nbsp;System.Text;using&nbsp;Microsoft.SqlServer.Server;public&nbsp;class&nbsp;UDF{ &nbsp;&nbsp;&nbsp;&nbsp;[SqlFunction(DataAccess&nbsp;=&nbsp;DataAccessKind.None,&nbsp;IsDeterministic=true)] &nbsp;&nbsp;&nbsp;&nbsp;public&nbsp;static&nbsp;SqlString&nbsp;Naturalize(string&nbsp;val) &nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(String.IsNullOrEmpty(val)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;val; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;while(val.Contains("&nbsp;&nbsp;")) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;val&nbsp;=&nbsp;val.Replace("&nbsp;&nbsp;",&nbsp;"&nbsp;"); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;const&nbsp;int&nbsp;maxLength&nbsp;=&nbsp;1000; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;const&nbsp;int&nbsp;padLength&nbsp;=&nbsp;25; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bool&nbsp;inNumber&nbsp;=&nbsp;false; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bool&nbsp;isDecimal&nbsp;=&nbsp;false; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;numStart&nbsp;=&nbsp;0; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;numLength&nbsp;=&nbsp;0; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;length&nbsp;=&nbsp;val.Length&nbsp;<&nbsp;maxLength&nbsp;?&nbsp;val.Length&nbsp;:&nbsp;maxLength; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//TODO:&nbsp;optimize&nbsp;this&nbsp;so&nbsp;that&nbsp;we&nbsp;exit&nbsp;for&nbsp;loop&nbsp;once&nbsp;sb.ToString()&nbsp;>=&nbsp;maxLength &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;sb&nbsp;=&nbsp;new&nbsp;StringBuilder(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for&nbsp;(var&nbsp;i&nbsp;=&nbsp;0;&nbsp;i&nbsp;<&nbsp;length;&nbsp;i++) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int&nbsp;charCode&nbsp;=&nbsp;(int)val[i]; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(charCode&nbsp;>=&nbsp;48&nbsp;&&&nbsp;charCode&nbsp;<=&nbsp;57) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(!inNumber) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;numStart&nbsp;=&nbsp;i; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;numLength&nbsp;=&nbsp;1; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;inNumber&nbsp;=&nbsp;true; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;continue; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;numLength++; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;continue; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(inNumber) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sb.Append(PadNumber(val.Substring(numStart,&nbsp;numLength),&nbsp;isDecimal,&nbsp;padLength)); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;inNumber&nbsp;=&nbsp;false; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;isDecimal&nbsp;=&nbsp;(charCode&nbsp;==&nbsp;46); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sb.Append(val[i]); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(inNumber) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sb.Append(PadNumber(val.Substring(numStart,&nbsp;numLength),&nbsp;isDecimal,&nbsp;padLength)); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;ret&nbsp;=&nbsp;sb.ToString(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(ret.Length&nbsp;>&nbsp;maxLength) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;ret.Substring(0,&nbsp;maxLength); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;ret; &nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;static&nbsp;string&nbsp;PadNumber(string&nbsp;num,&nbsp;bool&nbsp;isDecimal,&nbsp;int&nbsp;padLength) &nbsp;&nbsp;&nbsp;&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;isDecimal&nbsp;?&nbsp;num.PadRight(padLength,&nbsp;'0')&nbsp;:&nbsp;num.PadLeft(padLength,&nbsp;'0'); &nbsp;&nbsp;&nbsp;&nbsp;}}要注册这个以便您可以从SQL Server调用它,请在查询分析器中运行以下命令:CREATE&nbsp;ASSEMBLY&nbsp;SqlServerClr&nbsp;FROM&nbsp;'SqlServerClr.dll'&nbsp;--put&nbsp;the&nbsp;full&nbsp;path&nbsp;to&nbsp;DLL&nbsp;heregoCREATE&nbsp;FUNCTION&nbsp;Naturalize(@val&nbsp;as&nbsp;nvarchar(max))&nbsp;RETURNS&nbsp;nvarchar(1000)&nbsp;EXTERNAL&nbsp;NAME&nbsp;SqlServerClr.UDF.Naturalize go然后,您可以像这样使用它:select&nbsp;*from&nbsp;MyTableorder&nbsp;by&nbsp;dbo.Naturalize(MyTextField)注意:如果在SQL Server中出现错误,则禁用.NET Framework中的用户代码执行。启用“clr enabled”配置选项。,按照此处的说明启用它。在这样做之前,请确保考虑安全隐患。如果您不是数据库管理员,请确保在对服务器配置进行任何更改之前与管理员讨论此问题。注2:此代码不能正确支持国际化(例如,假设小数标记为“。”,未针对速度进行优化等。欢迎提出改进建议!编辑:将函数重命名为Naturalize而不是NaturalSort,因为它不进行任何实际排序。

绝地无双

order&nbsp;by&nbsp;LEN(value),&nbsp;value不完美,但在很多情况下效果很好。

45度呼吸

我知道这是一个老问题,但我刚刚遇到它,因为它没有得到一个公认的答案。我总是使用类似的方式:SELECT&nbsp;[Column]&nbsp;FROM&nbsp;[Table]ORDER&nbsp;BY&nbsp;RIGHT(REPLICATE('0',&nbsp;1000)&nbsp;+&nbsp;LTRIM(RTRIM(CAST([Column]&nbsp;AS&nbsp;VARCHAR(MAX)))),&nbsp;1000)唯一常见的问题是,如果您的列不会转换为VARCHAR(MAX),或者LEN([Column])> 1000(但如果您愿意,可以将1000更改为其他内容),但是可以根据您的需要使用这个粗略的想法。这也是比正常的ORDER BY [Column]更糟糕的性能,但它确实为你提供了OP中要求的结果。编辑:只是为了进一步说明,如果您有十进制值,例如有1,1.15和1.5(它们将排序为{1, 1.5, 1.15}),则上述将不起作用,因为这不是OP中要求的,但可以通过以下方式轻松完成:SELECT&nbsp;[Column]&nbsp;FROM&nbsp;[Table]ORDER&nbsp;BY&nbsp;REPLACE(RIGHT(REPLICATE('0',&nbsp;1000)&nbsp;+&nbsp;LTRIM(RTRIM(CAST([Column]&nbsp;AS&nbsp;VARCHAR(MAX))))&nbsp;+&nbsp;REPLICATE('0',&nbsp;100&nbsp;-&nbsp;CHARINDEX('.',&nbsp;REVERSE(LTRIM(RTRIM(CAST([Column]&nbsp;AS&nbsp;VARCHAR(MAX))))),&nbsp;1)),&nbsp;1000),&nbsp;'.',&nbsp;'0')结果:&nbsp;{1, 1.15, 1.5}而且仍然完全在SQL中。这不会对IP地址进行排序,因为您现在正在进入非常具体的数字组合,而不是简单的文本+数字。
打开App,查看更多内容
随时随地看视频慕课网APP
我要回答