时间戳

首页 > 解决方案 > SQL Server 中 Varchar 列中数值的奇怪排序行为

sql - SQL Server 中 Varchar 列中数值的奇怪排序行为

问题描述

有人可以解释一下这种奇怪的行为：

select a from (
    select '1' as a
    union all
    select '2' as a
    union all
    select '-3' as a
) as b
order by a desc

select a from (
    select '4' as a
    union all
    select '5' as a
    union all
    select '-3' as a
) as b
order by a desc

查询 1 的结果：

-3
2
1

查询 2 的结果：

5
4
-3

看起来-字符被忽略了。不过，我认为 SQL Server 基于 ASCII 代码订购 varchars。

所以预期的结果是：

2
1
-3   //ascii - is before 0-9

和：

 5
 4
-3  //ascii - is before 0-9

如果我在数字前添加一个字母，我会得到相同的结果：

select a from (
    select 'a1' as a
    union all
    select 'a2' as a
    union all
    select '-a3' as a
) as b
order by a desc

select a from (
    select 'a4' as a
    union all
    select 'a5' as a
    union all
    select '-a3' as a
) as b
order by a desc

标签： sqlsql-servertsql

解决方案

SQL Server 中的实际排序顺序完全取决于活动排序规则（默认排序规则或明确指定的排序规则）。

例如，如果您使用二进制排序规则，您将得到您对这种情况的期望：

select a from (
    select '1' as a
    union all
    select '2' as a
    union all
    select '-3' as a
) as b
order by a COLLATE Latin1_General_BIN desc
/* Result: 2, 1, -3  */

select a from (
    select '4' as a
    union all
    select '5' as a
    union all
    select '-3' as a
) as b
order by a COLLATE Latin1_General_BIN desc
/* Result: 5, 4, -3  */

要查看所有排序规则，请运行以下命令：

select * from sys.fn_helpcollations()

推荐阅读