sql-server - 在 SQL Server 查询期间更改排序规则？英语/西里尔语/欧盟语言的正确排序

Question

我正在研究一个以 SQL Server 作为后端的现有系统。我们开始以多种语言存储临时项目标题数据(包括西里尔字符的俄语和其他欧洲语言)。我的问题是关于对这些数据进行排序，在不更改排序规则的情况下按需更改排序顺序的选项有哪些？我可以决定查询本身的排序规则吗？

这是我正在尝试查看的示例查询:

SELECT STR FROM (
SELECT N'а01' AS STR UNION
SELECT N'б02' AS STR UNION
SELECT N'в03' AS STR UNION
SELECT N'г04' AS STR UNION
SELECT N'д05' AS STR UNION
SELECT N'е06' AS STR UNION
SELECT N'ё07' AS STR UNION
SELECT N'ж08' AS STR UNION
SELECT N'з09' AS STR UNION
SELECT N'и10' AS STR UNION
SELECT N'й11' AS STR UNION
SELECT N'к12' AS STR UNION
SELECT N'л13' AS STR UNION
SELECT N'м14' AS STR UNION
SELECT N'н15' AS STR UNION
SELECT N'о16' AS STR UNION
SELECT N'п17' AS STR UNION
SELECT N'р18' AS STR UNION
SELECT N'с19' AS STR UNION
SELECT N'т20' AS STR UNION
SELECT N'у21' AS STR UNION
SELECT N'ф22' AS STR UNION
SELECT N'х23' AS STR UNION
SELECT N'ц24' AS STR UNION
SELECT N'ч25' AS STR UNION
SELECT N'ш26' AS STR UNION
SELECT N'щ27' AS STR UNION
SELECT N'ъ28' AS STR UNION
SELECT N'ы29' AS STR UNION
SELECT N'ь30' AS STR UNION
SELECT N'э31' AS STR UNION
SELECT N'ю32' AS STR UNION
SELECT N'я33' AS STR UNION
SELECT N'a34' AS STR UNION
SELECT N'b35' AS STR UNION
SELECT N'c36' AS STR UNION
SELECT N'd37' AS STR UNION
SELECT N'e38' AS STR UNION
SELECT N'f39' AS STR UNION
SELECT N'g40' AS STR UNION
SELECT N'h41' AS STR UNION
SELECT N'i42' AS STR UNION
SELECT N'j43' AS STR UNION
SELECT N'k44' AS STR UNION
SELECT N'l45' AS STR UNION
SELECT N'm46' AS STR UNION
SELECT N'n47' AS STR UNION
SELECT N'o48' AS STR UNION
SELECT N'p49' AS STR UNION
SELECT N'q50' AS STR UNION
SELECT N'r51' AS STR UNION
SELECT N's52' AS STR UNION
SELECT N't53' AS STR UNION
SELECT N'u54' AS STR UNION
SELECT N'v55' AS STR UNION
SELECT N'w56' AS STR UNION
SELECT N'x57' AS STR UNION
SELECT N'y58' AS STR UNION
SELECT N'z59' AS STR UNION
SELECT N'A60' AS STR UNION
SELECT N'B70' AS STR UNION
SELECT N'C71' AS STR UNION
SELECT N'D72' AS STR UNION
SELECT N'E73' AS STR UNION
SELECT N'F74' AS STR UNION
SELECT N'G75' AS STR UNION
SELECT N'H76' AS STR UNION
SELECT N'I77' AS STR UNION
SELECT N'J78' AS STR UNION
SELECT N'K79' AS STR UNION
SELECT N'L80' AS STR UNION
SELECT N'M81' AS STR UNION
SELECT N'N82' AS STR UNION
SELECT N'O83' AS STR UNION
SELECT N'P84' AS STR UNION
SELECT N'Q85' AS STR UNION
SELECT N'R86' AS STR UNION
SELECT N'S87' AS STR UNION
SELECT N'T88' AS STR UNION
SELECT N'U89' AS STR UNION
SELECT N'V90' AS STR UNION
SELECT N'W91' AS STR UNION
SELECT N'X92' AS STR UNION
SELECT N'Y93' AS STR UNION
SELECT N'Z94' AS STR
) AS SUBTABLE
ORDER BY STR ASC

我得到的订单是这样的:

a34
A60
b35
B70
c36
C71
d37
D72
e38
E73
f39
F74
g40
G75
h41
H76
i42
I77
j43
J78
k44
K79
l45
L80
m46
M81
n47
N82
o48
O83
p49
P84
q50
Q85
r51
R86
s52
S87
t53
T88
u54
U89
v55
V90
w56
W91
x57
X92
y58
Y93
z59
Z94
а01
б02
в03
г04
д05
е06
ё07
ж08
з09
и10
й11
к12
л13
м14
н15
о16
п17
р18
с19
т20
у21
ф22
х23
ц24
ч25
ш26
щ27
ъ28
ы29
ь30
э31
ю32
я33

看起来一些西里尔字母与它们的英语对应字符相似，有没有办法让这些字符交错作为“A 到 Z”的一部分？

SQL Server 中还有哪些其他可用排序选项？

如果我无法在查询中执行此操作，那么在每列的基础上选择最可靠的排序规则是什么？

Answer 1

您可以选择西里尔字母排序规则，例如:

order by STR collate Cyrillic_General_CI_AI

可以通过调用以下命令找到完整的排序规则列表:

select * from ::fn_helpcollations()

CS 表示区分大小写，AI 表示不区分重音。