none
SQL Server对字符串排序的算法 RRS feed

  • 问题

  • 按照我的理解,对字符串的排序, 是按照低位优先排序的,比如

    SELECT * FROM (
    SELECT 'addd' AS t
    UNION
    SELECT 'baa' AS t
    ) A
    ORDER BY A.t
    COLLATE Chinese_PRC_CI_AS
    

    a开头的字符串始终在前面。但是我昨天被问到一个问题。如下所示.

    因为使用的Chinese_PRC_CI_AS,也就是GBK。从GBK的编码来看'-'是排在‘1’前面的,但是下面的查询结果确实‘11’在前面。

    谁能提供下SQL Server 对字符串排序的具体算法吗

    SELECT * FROM (
    SELECT '-11' AS t
    UNION
    SELECT '11' AS t
    ) A
    ORDER BY A.t
    COLLATE Chinese_PRC_CI_AS
    


    2017年11月14日 3:21

全部回复

  • 自己顶一下。
    2017年11月14日 4:03
  • 11 遇两个字符

    -11 是三个字符

    要比较 - 和 1 的先后顺序,你应该只写 - 和 1 两个来排序

    2017年11月14日 6:33
  • 似乎真有些问题

    SELECT * FROM(VALUES
    ('111'), ('1-1'), ('1-2'), ('112'),
    ('113'), ('1-3'), ('1-4'), ('114')
    ) A(t)
    ORDER BY t COLLATE Chinese_PRC_CI_AS 

    得到的结果居然是:

    t
    ----
    1-1
    111
    112
    113
    114
    1-2
    1-3
    1-4
    (8 行受影响)

    2017年11月14日 8:23
  • 因为啊,你这个不是Unicode。由于 SQL Server 排序规则对非 Unicode 数据和 Unicode
    数据使用不同的比较规则,因此对于相同数据的比较将会看到不同的结果,具体取决于基本数据类型。

    想不想时已是想,不如不想都不想。

    2017年11月14日 8:32
    版主
  • 或者你也可以用BIN,也会得到你希望的结果“从GBK的编码来看'-'是排在‘1’前面的”

    想不想时已是想,不如不想都不想。

    2017年11月14日 8:35
    版主
  • SELECT *,
    	DENSE_RANK()OVER(ORDER BY CONVERT(varchar(10), t) COLLATE Chinese_PRC_CI_AS) as [1],
    	DENSE_RANK()OVER(ORDER BY CONVERT(nvarchar(10), t) COLLATE Chinese_PRC_CI_AS) as [2]
    FROM(VALUES
    (N'110'), (N'1-0'), (N'1-a'), (N'11a'),
    (N'111'), (N'1-1'), (N'1-2'), (N'112'),
    (N'113'), (N'1-3'), (N'1-4'), (N'114')
    ) A(t)
    ORDER BY t COLLATE Chinese_PRC_CI_AS
    t    1                    2
    ---- -------------------- --------------------
    1-0  1                    1
    1-1  2                    2
    110  3                    3
    111  4                    4
    112  5                    5
    113  6                    6
    114  7                    7
    11a  8                    8
    1-2  9                    9
    1-3  10                   10
    1-4  11                   11
    1-a  12                   12
    (12 行受影响)

    没区别啊,用排序函数出来的顺序都一样,难道我哪里犯糊涂了?

    2017年11月14日 9:19
  • SELECT * FROM(VALUES('-1'),('11'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    SELECT * FROM(VALUES(N'-1'),(N'11'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    
    SELECT * FROM(VALUES('-2'),('12'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    SELECT * FROM(VALUES(N'-2'),(N'12'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    

    简单点说,对于上面两组查询,第一组和第2组的区别公仅是把最后一个字符从1换成2了,这个应该不影响结果顺序

    但第一组的顺序是: -1, 11(最后一个字符是0时也是这个顺序)

    第2组的顺序是: 12,-2(最后一个字符是2之后都是这个顺序)

    单独排序 -, 0, 1, 2 的顺序也是没问题的,所以第2组的顺序显得诡异

    2017年11月14日 9:37
  • 这个情况我知道,但是我想知道这中间的原理。如果是例外的字符‘aa11’ 和‘b1’这样的字符串。字符串长度不一样。但是他的比较就是按照GBK的排序规则来的,但‘11’ '-11'不是
    2017年11月15日 7:21
  • 那这个具体的比较规则是怎么样的,能详细说明下吗。我看字符类型的比较是正常的,数字的也没有问题。就是我举这个例子很有问题
    2017年11月15日 7:23
  • 因为啊,你这个不是Unicode。由于 SQL Server 排序规则对非 Unicode 数据和 Unicode
    数据使用不同的比较规则,因此对于相同数据的比较将会看到不同的结果,具体取决于基本数据类型。

    想不想时已是想,不如不想都不想。

    之前忘记引用了,,这个具体的规则是怎么样的?
    2017年11月16日 0:52
  • SELECT * FROM(VALUES('-1'),('11'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    SELECT * FROM(VALUES(N'-1'),(N'11'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    
    SELECT * FROM(VALUES('-2'),('12'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;
    SELECT * FROM(VALUES(N'-2'),(N'12'))A(t) ORDER BY t COLLATE Chinese_PRC_CI_AS;

    简单点说,对于上面两组查询,第一组和第2组的区别公仅是把最后一个字符从1换成2了,这个应该不影响结果顺序

    但第一组的顺序是: -1, 11(最后一个字符是0时也是这个顺序)

    第2组的顺序是: 12,-2(最后一个字符是2之后都是这个顺序)

    单独排序 -, 0, 1, 2 的顺序也是没问题的,所以第2组的顺序显得诡异

    其实也不是诡异,,这里面应该是有一个规则的。就是不知道这个规则是什么样的
    2017年11月16日 0:55
  • 目测是做了数学运算,谁有兴趣多测试一些场景。

    想不想时已是想,不如不想都不想。

    2017年11月16日 5:17
    版主
  • 目测是做了数学运算,谁有兴趣多测试一些场景。

    想不想时已是想,不如不想都不想。

    我之前的推测是把这几个字符对应的10进制加起来然后根据大小来排序

    比如:-11 是 45 49 49, 而 11对应的是49 49 ,所以11 是小于-11的,11排在前面.

    但是这个规则对于纯字符和的情况就不适用。

    2017年11月16日 8:52
  • Hi all,

    经过我的大量测试,大概找出了排序规则,字符“-”的排序方式和其他的字符不一样,排序规则是先把字符串中的“-”去除然后按照字符的顺序进行排序,然后如果有几个字符串中出去“-”的字符相同且顺序一致,则按照“-”在字符中的位置进行排序,位置越靠后,顺序越往后,以下是测试结果:

    首先我们运行一下这个code:

    SELECT * FROM(VALUES
    ('0111'), ('01-1'), ('01-2'), ('0112'),
    ('0113'), ('01-3'), ('01-4'),('01-0'),('01--'),('011-'),('0--1'),('0-11'),('00--'),('0-1'),('-00'),('-0-1-0'),('-010'),('-010---')
    ) A(t)
    ORDER BY t COLLATE Chinese_PRC_CI_AS

    得到这个结果

    我们这时候把“-”忽略掉,就会找到相应的规律,然后我们研究一下“-”对顺序的影响:

    SELECT * FROM(VALUES
    ('-0-1'),('-01-'),('01--'),('--01'),('0--1'),('0-1-')
    ) A(t)
    ORDER BY t COLLATE Chinese_PRC_CI_AS

    “-”的位置越往后,顺序也越往后。字符串结尾的“-”也会被忽略。当所有的字符都一致,只有结尾的“-”的数量有区别,则数量越多,排序越后。

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2017年11月22日 7:54
    版主
  • 不管到底啥规则,这都是一个bug,谁去report一下?

    想不想时已是想,不如不想都不想。

    2017年11月28日 6:33
    版主
  • 非常感谢您的回复,因为我前两天出差了,没有来得及回复。微软对这种规则有具体的说明吗
    2017年12月4日 5:14
  • 我也感觉这个挺规则挺奇怪,不知是有意为之还是bug
    2017年12月4日 5:15
  • Hi owen_zeng,

    我没有找到相关的说明,这个是测试出来的,我倾向于这是有意为之的而不是一个bug,我测试了好几个版本的SQL Server,得出来的排序方法都是一样的。

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2017年12月4日 5:39
    版主
  • Hi owen_zeng,

    我没有找到相关的说明,这个是测试出来的,我倾向于这是有意为之的而不是一个bug,我测试了好几个版本的SQL Server,得出来的排序方法都是一样的。

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    感谢你的回复,不过如果有文档可以看看,就更好了,找遍了很多地方都没有这个对这个的说明,,
    2017年12月4日 9:08