SQLSERVER的排序规则

SQL 　 SERVER 的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到 : SQL SERVER 数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误：

 

“无法解决 equal to 操作的排序规则冲突。”

 

一 . 错误分析：

　　这个错误是因为排序规则不一致造成的，我们做个测试，比如：

create table #t1(

name varchar(20) collate Albanian_CI_AI_WS,

value int)

 

create table #t2(

name varchar(20) collate Chinese_PRC_CI_AI_WS,

value int )

 

表建好后，执行连接查询：

 

select * from #t1 A inner join #t2 B on A.name=B.name

 

这样，错误就出现了：

 

服务器 : 消息 446 ，级别 16 ，状态 9 ，行 1

无法解决 equal to 操作的排序规则冲突。

　　要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就

不再出现了。语句这样写：

 

select *

from #t1 A inner join #t2 B

on A.name=B.name collate Chinese_PRC_CI_AI_WS

 

二 . 排序规则简介：

 

什么叫排序规则呢？ MS 是这样描述的： " 在 Microsoft SQL Server 2000 中，

字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存

储和比较字符所使用的规则。 "

　　在查询分析器内执行下面语句，可以得到 SQL 　 SERVER 支持的所有排序规则。

 

　　　　 select * from ::fn_helpcollations()

 

排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。

如：

　　 Chinese_PRC_CS_AI_WS

前半部份：指 UNICODE 字符集， Chinese_PRC_ 指针对大陆简体字 UNICODE 的排序规则。

排序规则的后半部份即后缀 含义：

　　 _BIN 二进制排序

　　 _CI(CS) 是否区分大小写， CI 不区分， CS 区分

　　 _AI(AS) 是否区分重音， AI 不区分， AS 区分　　　

　　 _KI(KS) 是否区分假名类型 ,KI 不区分， KS 区分　

_WI(WS) 是否区分宽度 WI 不区分， WS 区分　

 

区分大小写 : 如果想让比较将大写字母和小写字母视为不等，请选择该选项。

区分重音 : 如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，比较还将重音不同的字母视为不等。

区分假名 : 如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。

区分宽度 : 如果想让比较将半角字符和全角字符视为不等，请选择该选项

 

 

三 . 排序规则的应用：

　　 SQL SERVER 提供了大量的 WINDOWS 和 SQLSERVER 专用的排序规则，但它的应用往往被开发人员所忽略。其实它在实践中大有用处。

 

　　例 1: 让表 NAME 列的内容按拼音排序：

 

create table #t(id int,name varchar(20))

insert #t select 1,' 中 '

union all select 2,' 国 '

union all select 3,' 人 '

union all select 4,' 阿 '

 

select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS

drop table #t

/* 结果：

id name

----------- --------------------

4 阿

2 国

3 人

1 中

*/

 

　　例 2 ：让表 NAME 列的内容按姓氏笔划排序：

 

create table #t(id int,name varchar(20))

 

insert #t select 1,' 三 '

union all select 2,' 乙 '

union all select 3,' 二 '

union all select 4,' 一 '

union all select 5,' 十 '

select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS

drop table #t

/* 结果：

id name

----------- --------------------

4 一

2 乙

3 二

5 十

1 三

*/

 

四 . 在实践中排序规则应用的扩展

　　 SQL SERVER 汉字排序规则可以按拼音、笔划等排序，那么我们如何利用这种功能来处理汉字的一些难题呢？我现在举个例子：

 

　　　　　　　　　　用排序规则的特性计算汉字笔划

 

　　要计算汉字笔划，我们得先做准备工作，我们知道， WINDOWS 多国汉字， UNICODE 目前收录汉字共 20902 个。简体 GBK 码汉字 UNICODE 值从 19968 开始。

　　首先，我们先用 SQLSERVER 方法得到所有汉字，不用字典，我们简单利用 SQL 语句就可以得到：

 

select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b

 

再用以下语句，我们就得到所有汉字，它是按 UNICODE 值排序的：

 

　　 select code,nchar(code) as CNWord from #t

 

　　然后，我们用 Select 语句，让它按笔划排序。

 

select code,nchar(code) as CNWord

from #t

order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code

 

结果：

code CNWord

----------- ------

19968 一

20008 丨

20022 丶

20031 丿

20032 乀

20033 乁

20057 乙

20058 乚

20059 乛

20101 亅

19969 丁

..........

 

　 从上面的结果，我们可以清楚的看到，一笔的汉字， code 是从 19968 到 20101 ，从小到大排，但到了二笔汉字的第一个字“丁”， CODE 为 19969 ，就不按顺序而重新开始了。有了这结果，我们就可以轻松的用 SQL 语句得到每种笔划汉字归类的第一个或最后一个汉字。下面用语句得到最后一个汉字：

 

create table #t1(id int identity,code int,cnword nvarchar(2))

 

insert #t1(code,cnword)

select code,nchar(code) as CNWord from #t

order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code

 

select A.cnword

from #t1 A

left join #t1 B on A.id=B.id-1 and A.code<B.code

where B.code is null

order by A.id

 

得到 36 个汉字，每个汉字都是每种笔划数按 Chinese_PRC_Stroke_CS_AS_KS_WS 排序规则排序后的最后一个汉字：

 

亅阝马风龙齐龟齿鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘

 

　　上面可以看出：“亅”是所有一笔汉字排序后的最后一个字，“阝”是所有二笔汉字排序后的最后一个字 ...... 等等。

　　但同时也发现，从第 33 个汉字“龗 (33 笔 ) ”后面的笔划有些乱，不正确。但没关系，比“龗”笔划多的只有四个汉字，我们手工加上：齾 35 笔，齉 36 笔，靐 39 笔，龘 64 笔

 

建汉字笔划表（ TAB_HZBH ）：

create table tab_hzbh(id int identity,cnword nchar(1))

-- 先插入前 33 个汉字

insert tab_hzbh

select top 33 A.cnword

from #t1 A

left join #t1 B on A.id=B.id-1 and A.code<B.code

where B.code is null

order by A.id

-- 再加最后四个汉字

set identity_insert tab_hzbh on

go

insert tab_hzbh(id,cnword)

　　　　　 select 35,N' 齾 '

union all select 36,N' 齉 '

union all select 39,N' 靐 '

union all select 64,N' 龘 '

go

set identity_insert tab_hzbh off

go

 

　　到此为止，我们可以得到结果了，比如我们想得到汉字“国”的笔划：

 

declare @a nchar(1)

set @a=' 国 '

select top 1 id

from tab_hzbh

where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS

order by id

 

id

-----------

8

( 结果：汉字“国”笔划数为 8)

 

　　上面所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固定表，为了通用和代码转移方便，把表 tab_hzbh 的内容写在语句内，然后计算用户输入一串汉字的总笔划：

 

create function fun_getbh(@str nvarchar(4000))

returns int

as

begin

declare @word nchar(1),@n int

set @n=0

while len(@str)>0

begin

set @word=left(@str,1)

-- 如果非汉字，笔划当 0 计

set @n=@n+(case when unicode(@word) between 19968 and 19968+20901

then (select top 1 id from (

select 1 as id,N' 亅 ' as word

union all select 2,N' 阝 '

union all select 3,N' 马 '

union all select 4,N' 风 '

union all select 5,N' 龙 '

union all select 6,N' 齐 '

union all select 7,N' 龟 '

union all select 8,N' 齿 '

union all select 9,N' 鸩 '

union all select 10,N' 龀 '

union all select 11,N' 龛 '

union all select 12,N' 龂 '

union all select 13,N' 龆 '

union all select 14,N' 龈 '

union all select 15,N' 龊 '

union all select 16,N' 龍 '

union all select 17,N' 龠 '

union all select 18,N' 龎 '

union all select 19,N' 龐 '

union all select 20,N' 龑 '

union all select 21,N' 龡 '

union all select 22,N' 龢 '

union all select 23,N' 龝 '

union all select 24,N' 齹 '

union all select 25,N' 龣 '

union all select 26,N' 龥 '

union all select 27,N' 齈 '

union all select 28,N' 龞 '

union all select 29,N' 麷 '

union all select 30,N' 鸞 '

union all select 31,N' 麣 '

union all select 32,N' 龖 '

union all select 33,N' 龗 '

union all select 35,N' 齾 '

union all select 36,N' 齉 '

union all select 39,N' 靐 '

union all select 64,N' 龘 '

) T

where word>=@word collate Chinese_PRC_Stroke_CS_AS_KS_WS

order by id ASC) else 0 end)

set @str=right(@str,len(@str)-1)

end

return @n

end

 

-- 函数调用实例：

select dbo.fun_getbh(' 中华人民共和国 '),dbo.fun_getbh(' 中華人民共和國 ')

　

　　执行结果：笔划总数分别为 39 和 46 ，简繁体都行。

 

当然，你也可以把上面“ UNION 　 ALL ”内的汉字和笔划改存在固定表内，在汉字列建 CLUSTERED INDEX ，列排序规则设定为：

　　　 Chinese_PRC_Stroke_CS_AS_KS_WS

这样速度更快。如果你用的是 BIG5 码的操作系统，你得另外生成汉字，方法一样。但有一点要记住：这些汉字是通过 SQL 语句 Select 出来的，不是手工输入的，更不是查字典得来的，因为新华字典毕竟不同于 UNICODE 字符集，查字典的结果会不正确。

 

　　

　　　　用排序规则的特性得到汉字拼音首字母

 

　　用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：

 

create function fun_getPY(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @word nchar(1),@PY nvarchar(4000)

set @PY=''

while len(@str)>0

begin

set @word=left(@str,1)

-- 如果非汉字字符，返回原字符

set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901

then (select top 1 PY from (

select 'A' as PY,N' 驁 ' as word

union all select 'B',N' 簿 '

union all select 'C',N' 錯 '

union all select 'D',N' 鵽 '

union all select 'E',N' 樲 '

union all select 'F',N' 鰒 '

union all select 'G',N' 腂 '

union all select 'H',N' 夻 '

union all select 'J',N' 攈 '

union all select 'K',N' 穒 '

union all select 'L',N' 鱳 '

union all select 'M',N' 旀 '

union all select 'N',N' 桛 '

union all select 'O',N' 漚 '

union all select 'P',N' 曝 '

union all select 'Q',N' 囕 '

union all select 'R',N' 鶸 '

union all select 'S',N' 蜶 '

union all select 'T',N' 籜 '

union all select 'W',N' 鶩 '

union all select 'X',N' 鑂 '

union all select 'Y',N' 韻 '

union all select 'Z',N' 咗 '

) T

where word>=@word collate Chinese_PRC_CS_AS_KS_WS

order by PY ASC) else @word end)

set @str=right(@str,len(@str)-1)

end

return @PY

end

 

-- 函数调用实例：

select dbo.fun_getPY(' 中华人民共和国 '),dbo.fun_getPY(' 中華人民共和國 ')

结果都为： ZHRMGHG

 

　 你若有兴趣，也可用相同的方法，扩展为得到汉字全拼的函数，甚至还可以得到全拼的读音声调，不过全拼分类大多了。得到全拼最好是用对照表，两万多汉字搜索速度很快，用对照表还可以充分利用表的索引。排序规则还有很多其它的巧妙用法。欢迎大家共同探讨。