本文介绍了将nvarchar列排序为整数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在nvarchar列中混合了数据(单词和数字).这是按数字顺序对此列中的数据进行排序的最快方法.

I have mixed data i nvarchar column (words and numbers).Which is fastest way to sort data in this column in Numeric Order.

结果示例:

  • 1
  • 2
  • 3
  • ...
  • 10
  • 11
  • ...
  • aaaa
  • aaab
  • b
  • ba
  • ba
  • ...
  • 1
  • 2
  • 3
  • ...
  • 10
  • 11
  • ...
  • aaaa
  • aaab
  • b
  • ba
  • ba
  • ...

推荐答案

使用此:

ORDER BY
    CASE WHEN ISNUMERIC(column) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(column) = 1 THEN CAST(column AS INT) ELSE 0 END,
    column

这按预期工作.

注意:您说最快的方式.这条sql对我来说生成起来很快,但是执行计划显示了表扫描,然后进行了标量计算.这可能会产生一个临时结果,其中包含该列的所有值以及一些用于ISNUMERIC结果的额外临时列.执行起来可能并不快.

Note: You say fastest way. This sql was fast for me to produce, but the execution plan shows a table-scan, followed by a scalar computation. This could possibly produce a temporary result containing all the values of that column with some extra temporary columns for the ISNUMERIC results. It might not be fast to execute.

这篇关于将nvarchar列排序为整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 05:28