SQL Server的排序規(guī)則很多人使用的并不是很多，對(duì)于初學(xué)者來(lái)講會(huì)感覺(jué)特別陌生，但其中有一個(gè)錯(cuò)誤很多人都是經(jīng)常碰到的: SQL Server數(shù)據(jù)庫(kù)，在跨庫(kù)多表連接查詢時(shí)，若兩數(shù)據(jù)庫(kù)默認(rèn)字符集不同，系統(tǒng)就會(huì)返回這樣的錯(cuò)誤： “無(wú)法解決 equal to 操作的排序規(guī)則沖突。”
一、錯(cuò)誤分析：
這個(gè)錯(cuò)誤是因?yàn)榕判蛞?guī)則不一致造成的，測(cè)試如下：
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 ) |
表建好后，再來(lái)執(zhí)行連接查詢：
select * from #t1 A inner join #t2 B on A.name=B.name |
此時(shí)，錯(cuò)誤就出現(xiàn)了：
服務(wù)器: 消息 446，級(jí)別 16，狀態(tài) 9，行 1
無(wú)法解決 equal to 操作的排序規(guī)則沖突。
要排除這個(gè)錯(cuò)誤，最簡(jiǎn)單方法是，表連接時(shí)指定它的排序規(guī)則，這樣錯(cuò)誤就不再出現(xiàn)了。語(yǔ)句這樣寫：
select * from #t1 A inner join #t2 B on A.name=B.name collate Chinese_PRC_CI_AI_WS |
二.排序規(guī)則簡(jiǎn)介：
什么叫排序規(guī)則呢？MS是這樣描述的："在 Microsoft SQL Server 2000 中，字符串的物理存儲(chǔ)由排序規(guī)則控制。排序規(guī)則指定表示每個(gè)字符的位模式以及存儲(chǔ)和比較字符所使用的規(guī)則。"在查詢分析器內(nèi)執(zhí)行下面語(yǔ)句，可以得到SQL SERVER支持的所有排序規(guī)則。
select * from ::fn_helpcollations() |
排序規(guī)則名稱由兩部份構(gòu)成，前半部份是指本排序規(guī)則所支持的字符集。如：
Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指針對(duì)大陸簡(jiǎn)體字UNICODE的排序規(guī)則。
排序規(guī)則的后半部份即后綴 含義：
_BIN 二進(jìn)制排序
_CI(CS) 是否區(qū)分大小寫，CI不區(qū)分，CS區(qū)分
_AI(AS) 是否區(qū)分重音，AI不區(qū)分，AS區(qū)分
_KI(KS) 是否區(qū)分假名類型,KI不區(qū)分，KS區(qū)分
_WI(WS) 是否區(qū)分寬度 WI不區(qū)分，WS區(qū)分
區(qū)分大小寫:如果想讓比較將大寫字母和小寫字母視為不等，請(qǐng)選擇該選項(xiàng)。
區(qū)分重音:如果想讓比較將重音和非重音字母視為不等，請(qǐng)選擇該選項(xiàng)。如果選擇該選項(xiàng)，
比較還將重音不同的字母視為不等。
區(qū)分假名:如果想讓比較將片假名和平假名日語(yǔ)音節(jié)視為不等，請(qǐng)選擇該選項(xiàng)。
區(qū)分寬度:如果想讓比較將半角字符和全角字符視為不等，請(qǐng)選擇該選項(xiàng)。
三.排序規(guī)則的應(yīng)用：
SQL Server提供了大量的WINDOWS和SQLSERVER專用的排序規(guī)則，但它的應(yīng)用往往被開(kāi)發(fā)人員所忽略。其實(shí)它在實(shí)踐中大有用處。
例1:讓表NAME列的內(nèi)容按拼音排序：
create table #t(id int,name varchar(20))insert #t select 1,'中'union all select 2,'國(guó)'union all select 3,'人'union all select 4,'阿'select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS drop table #t/*結(jié)果：id name ----------- -------------------- 4 阿2 國(guó)3 人1 中*/ |
例2：讓表NAME列的內(nèi)容按姓氏筆劃排序：
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 |
/*結(jié)果：
id name ----------- -------------------- 4 一2 乙3 二5 十1 三*/ |
四.在實(shí)踐中排序規(guī)則應(yīng)用的擴(kuò)展
SQL Server漢字排序規(guī)則可以按拼音、筆劃等排序，那么我們?nèi)绾卫眠@種功能來(lái)處理漢字的一些難題呢？我現(xiàn)在舉個(gè)例子： 用排序規(guī)則的特性計(jì)算漢字筆劃
要計(jì)算漢字筆劃，我們得先做準(zhǔn)備工作，我們知道，WINDOWS多國(guó)漢字，UNICODE目前收錄漢字共20902個(gè)。簡(jiǎn)體GBK碼漢字UNICODE值從19968開(kāi)始。
首先，我們先用SQLSERVER方法得到所有漢字，不用字典，我們簡(jiǎn)單利用SQL語(yǔ)句就可以得到：
select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b |
再用以下語(yǔ)句，我們就得到所有漢字，它是按UNICODE值排序的：
select code,nchar(code) as CNWord from #t 然后，我們用Select語(yǔ)句，讓它按筆劃排序。select code,nchar(code) as CNWord from #t order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code |
結(jié)果：
code CNWord ----------- ------ 19968 一20008 丨20022 丶20031 丿20032 乀20033 乁20057 乙20058 乚20059 乛20101 亅19969 丁.......... |
從上面的結(jié)果，我們可以清楚的看到，一筆的漢字，code是從19968到20101，從小到大排，但到了二筆漢字的第一個(gè)字“丁”，CODE為19969，就不按順序而重新開(kāi)始了。有了這結(jié)果，我們就可以輕松的用SQL語(yǔ)句得到每種筆劃漢字歸類的第一個(gè)或最后一個(gè)漢字。
下面用語(yǔ)句得到最后一個(gè)漢字：
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,codeselect A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.code<B.code where B.code is nullorder by A.id |
得到36個(gè)漢字，每個(gè)漢字都是每種筆劃數(shù)按Chinese_PRC_Stroke_CS_AS_KS_WS排序規(guī)則排序后的最后一個(gè)漢字：
亅阝馬風(fēng)龍齊龜齒鴆齔龕龂齠齦齪龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘
上面可以看出：“亅”是所有一筆漢字排序后的最后一個(gè)字，“阝”是所有二筆漢字排序后的最后一個(gè)字......等等。
但同時(shí)也發(fā)現(xiàn)，從第33個(gè)漢字“龗(33筆)”后面的筆劃有些亂，不正確。但沒(méi)關(guān)系，比“龗”筆劃多的只有四個(gè)漢字，我們手工加上：齾35筆，齉36筆，靐39筆，龘64筆
建漢字筆劃表（TAB_HZBH）：
create table tab_hzbh(id int identity,cnword nchar(1))--先插入前33個(gè)漢字insert tab_hzbhselect 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 nullorder by A.id--再加最后四個(gè)漢字set identity_insert tab_hzbh ongoinsert tab_hzbh(id,cnword) select 35,N'齾'union all select 36,N'齉'union all select 39,N'靐'union all select 64,N'龘'goset identity_insert tab_hzbh offgo |
到此為止，我們可以得到結(jié)果了，比如我們想得到漢字“國(guó)”的筆劃：
declare @a nchar(1)set @a='國(guó)'select top 1 id from tab_hzbh where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WSorder by idid ----------- 8(結(jié)果：漢字“國(guó)”筆劃數(shù)為8) |
上面所有準(zhǔn)備過(guò)程，只是為了寫下面這個(gè)函數(shù)，這個(gè)函數(shù)撇開(kāi)上面建的所有臨時(shí)表和固定表，為了通用和代碼轉(zhuǎn)移方便，把表tab_hzbh的內(nèi)容寫在語(yǔ)句內(nèi)，然后計(jì)算用戶輸入一串漢字的總筆劃：
create function fun_getbh(@str nvarchar(4000))returns intasbegindeclare @word nchar(1),@n intset @n=0while len(@str)>0beginset @word=left(@str,1)--如果非漢字，筆劃當(dāng)0計(jì)set @n=@n+(case when unicode(@word) between 19968 and 19968+20901then (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'風(fēng)' 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_WSorder by id ASC) else 0 end)set @str=right(@str,len(@str)-1)endreturn @nend |
函數(shù)調(diào)用實(shí)例：
select dbo.fun_getbh('中華人民共和國(guó)'),dbo.fun_getbh('中華人民共和國(guó)')
執(zhí)行結(jié)果：筆劃總數(shù)分別為39和46，簡(jiǎn)繁體都行。
當(dāng)然，你也可以把上面“UNION ALL”內(nèi)的漢字和筆劃改存在固定表內(nèi)，在漢字列建CLUSTERED INDEX，列排序規(guī)則設(shè)定為：
Chinese_PRC_Stroke_CS_AS_KS_WS
這樣速度更快。如果你用的是BIG5碼的操作系統(tǒng)，你得另外生成漢字，方法一樣。但有一點(diǎn)要記住：這些漢字是通過(guò)SQL語(yǔ)句Select出來(lái)的，不是手工輸入的，更不是查字典得來(lái)的，因?yàn)樾氯A字典畢竟不同于UNICODE字符集，查字典的結(jié)果會(huì)不正確。
用排序規(guī)則的特性得到漢字拼音首字母
用得到筆劃總數(shù)相同的方法，我們也可以寫出求漢字拼音首字母的函數(shù)。如下：
create function fun_getPY(@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @word nchar(1),@PY nvarchar(4000)set @PY=''while len(@str)>0beginset @word=left(@str,1)--如果非漢字字符，返回原字符set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901then (select top 1 PY from (select 'A' as PY,N'驁' as wordunion all select 'B',N'簿'union all select 'C',N'錯(cuò)'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)endreturn @PYend |
函數(shù)調(diào)用實(shí)例：
select dbo.fun_getPY('中華人民共和國(guó)'),dbo.fun_getPY('中華人民共和國(guó)')
結(jié)果都是：ZHRMGHG
大家如果有興趣，可以使用相同的方法，擴(kuò)展得到漢字全拼的函數(shù)，甚至你還可以得到全拼的讀音聲調(diào)，不過(guò)全拼分類大多了。得到全拼最好還是用對(duì)照表，兩萬(wàn)多漢字搜索速度顯然很快，另外，用對(duì)照表還可以充分利用表的索引。
