排序问题

[dongshanyu]

SQL SERVER 2008，Chinese_PRC_CI_AI
相关SQL如下：

1.含"-"的字符串排序和期望的一样
SELECT A.T1,ASCII(A.T1) AS T1_ASCII
FROM
(
SELECT '0' AS T1
UNION ALL SELECT '1' AS T1
UNION ALL SELECT '2' AS T1
UNION ALL SELECT '3' AS T1
UNION ALL SELECT '4' AS T1
UNION ALL SELECT '-' AS T1
UNION ALL SELECT '5' AS T1
UNION ALL SELECT '6' AS T1
UNION ALL SELECT '7' AS T1
UNION ALL SELECT '8' AS T1
UNION ALL SELECT '9' AS T1
) A
WHERE 1 = 1
ORDER BY T1

2.含"-"的字符串排序和期望的不同
SELECT A.T1,ASCII(A.T1) AS T1_ASCII
FROM
(
SELECT '01' AS T1
UNION ALL SELECT '11' AS T1
UNION ALL SELECT '21' AS T1
UNION ALL SELECT '31' AS T1
UNION ALL SELECT '41' AS T1
UNION ALL SELECT '-1' AS T1
UNION ALL SELECT '51' AS T1
UNION ALL SELECT '61' AS T1
UNION ALL SELECT '71' AS T1
UNION ALL SELECT '81' AS T1
UNION ALL SELECT '91' AS T1
) A
WHERE 1 = 1
ORDER BY T1

3.含"-"的字符串排序和期望的不同
SELECT A.T1,ASCII(A.T1) AS T1_ASCII
FROM
(
SELECT 'A01' AS T1
UNION ALL SELECT 'A11' AS T1
UNION ALL SELECT 'A21' AS T1
UNION ALL SELECT 'A31' AS T1
UNION ALL SELECT 'A41' AS T1
UNION ALL SELECT 'A-1' AS T1
UNION ALL SELECT 'A51' AS T1
UNION ALL SELECT 'A61' AS T1
UNION ALL SELECT 'A71' AS T1
UNION ALL SELECT 'A81' AS T1
UNION ALL SELECT 'A91' AS T1
) A
WHERE 1 = 1
ORDER BY T1

哪位知道原因在哪？

[阳泉酒家小当家]

之所以，会返回这种顺序，肯定是由于 排序规则的影响，而这种 Chinese_PRC_CI_AI排序规则，肯定也是按照一个规则来排序的，要弄清楚到底是要如何排的，这个比较麻烦。 不过我们可以通过直接指定参数，来确定用什么排序规则的。

[阳泉酒家小当家]

比如，我们可以指定collate关键字 来显式的指定排序规则，下面的结果就一样了：

SELECT A.T1,ASCII(A.T1) AS T1_ASCII
FROM 
(
  SELECT '01' AS T1
UNION ALL SELECT '11' AS T1
UNION ALL SELECT '21' AS T1
UNION ALL SELECT '31' AS T1
UNION ALL SELECT '41' AS T1
UNION ALL SELECT '-1' AS T1
UNION ALL SELECT '51' AS T1
UNION ALL SELECT '61' AS T1
UNION ALL SELECT '71' AS T1
UNION ALL SELECT '81' AS T1
UNION ALL SELECT '91' AS T1
) A
WHERE  1 = 1
ORDER BY T1 collate chinese_prc_bin
/*
T1	T1_ASCII
-1	45
01	48
11	49
21	50
31	51
41	52
51	53
61	54
71	55
81	56
91	57
*/



SELECT A.T1,ASCII(A.T1) AS T1_ASCII
FROM 
(
  SELECT 'A01' AS T1
UNION ALL SELECT 'A11' AS T1
UNION ALL SELECT 'A21' AS T1
UNION ALL SELECT 'A31' AS T1
UNION ALL SELECT 'A41' AS T1
UNION ALL SELECT 'A-1' AS T1
UNION ALL SELECT 'A51' AS T1
UNION ALL SELECT 'A61' AS T1
UNION ALL SELECT 'A71' AS T1
UNION ALL SELECT 'A81' AS T1
UNION ALL SELECT 'A91' AS T1
) A
WHERE  1 = 1
ORDER BY T1 collate chinese_prc_bin
/*
T1	T1_ASCII
A-1	65
A01	65
A11	65
A21	65
A31	65
A41	65
A51	65
A61	65
A71	65
A81	65
A91	65
*/

[dongshanyu]

谢谢俩位的回复，通过ORDER BY T1 COLLATE Chinese_PRC_BIN ASC可达到我要的效果，再次感谢俩位。

[Tiger_Zhao]

你在同一个目录下创建几个空白文件，用它的排序来说明。
自从文件名中的数字可以按值的大小进行排序后，字符串比较会进行拆段、然后再分别按字符串/数值进行比较。

第一组（只有一段）

01.txt
-1.txt
9.txt
11.txt

0开头的数字当作编码，按字符串比较。
而-1是当作数字的。

第二组（多段）

A01.txt
A9.txt
A11.txt
A-1.txt
A-9.txt
A-11

这时因为前面是字符串，所有-被归入前面的字符串，后面的数字按正整数来比较。

而你测试的结果说明，数据库虽然是类似的做法，拆段规则可能不一样。

[Tiger_Zhao]

这种编码字段建议指定排序为 Chinese_PRC_BIN，并且字母一律用大写。