古风小说君子以泽,盗墓笔记小说全集

主頁 > 知識庫 > 整理一下SQLSERVER的排序規(guī)則

整理一下SQLSERVER的排序規(guī)則

SQL　SERVER的排序規(guī)則平時使用不是很多，也許不少初學(xué)者還比較陌生，但有
一個錯誤大家應(yīng)是經(jīng)常碰到:  SQL  SERVER數(shù)據(jù)庫，在跨庫多表連接查詢時，若兩數(shù)據(jù)
庫默認(rèn)字符集不同，系統(tǒng)就會返回這樣的錯誤：

                     “無法解決  equal  to  操作的排序規(guī)則沖突?！?

一.錯誤分析：
　　這個錯誤是因為排序規(guī)則不一致造成的，我們做個測試，比如：
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  )

表建好后，執(zhí)行連接查詢：

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

這樣，錯誤就出現(xiàn)了：

                     服務(wù)器:  消息  446，級別  16，狀態(tài)  9，行  1
                     無法解決  equal  to  操作的排序規(guī)則沖突。
　　要排除這個錯誤，最簡單方法是，表連接時指定它的排序規(guī)則，這樣錯誤就
不再出現(xiàn)了。語句這樣寫：

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

二.排序規(guī)則簡介：

       什么叫排序規(guī)則呢？MS是這樣描述的："在  Microsoft  SQL  Server  2000  中，
字符串的物理存儲由排序規(guī)則控制。排序規(guī)則指定表示每個字符的位模式以及存
儲和比較字符所使用的規(guī)則。"
　　在查詢分析器內(nèi)執(zhí)行下面語句，可以得到SQL　SERVER支持的所有排序規(guī)則。

　　　　select  *  from  ::fn_helpcollations()

排序規(guī)則名稱由兩部份構(gòu)成，前半部份是指本排序規(guī)則所支持的字符集。
如：
　　Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指針對大陸簡體字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ū)分重音:如果想讓比較將重音和非重音字母視為不等，請選擇該選項。如果選擇該選項，
                 比較還將重音不同的字母視為不等。
區(qū)分假名:如果想讓比較將片假名和平假名日語音節(jié)視為不等，請選擇該選項。
區(qū)分寬度:如果想讓比較將半角字符和全角字符視為不等，請選擇該選項

三.排序規(guī)則的應(yīng)用：
　　SQL  SERVER提供了大量的WINDOWS和SQLSERVER專用的排序規(guī)則，但它的應(yīng)用往往
被開發(fā)人員所忽略。其實它在實踐中大有用處。

　　例1:讓表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,'阿'

select  *  from  #t  order  by  name  collate  Chinese_PRC_CS_AS_KS_WS
drop  table  #t
/*結(jié)果：
id                    name
-----------  --------------------
4                      阿
2                      國
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                      三
*/

四.在實踐中排序規(guī)則應(yīng)用的擴(kuò)展
　　SQL  SERVER漢字排序規(guī)則可以按拼音、筆劃等排序，那么我們?nèi)绾卫眠@種功能
來處理漢字的一些難題呢？我現(xiàn)在舉個例子：

　　　　　　　　　　用排序規(guī)則的特性計算漢字筆劃

　　要計算漢字筆劃，我們得先做準(zhǔn)備工作，我們知道，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

結(jié)果：
code                CNWord
-----------  ------
19968              一
20008              丨
20022              丶
20031              丿
20032              乀
20033              乁
20057              乙
20058              乚
20059              乛
20101              亅
19969              丁
..........

　    從上面的結(jié)果，我們可以清楚的看到，一筆的漢字，code是從19968到20101，從小到大排，但到
了二筆漢字的第一個字“丁”，CODE為19969，就不按順序而重新開始了。有了這結(jié)果，我們就可以輕
松的用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.codeB.code
where  B.code  is  null
order  by  A.id

得到36個漢字，每個漢字都是每種筆劃數(shù)按Chinese_PRC_Stroke_CS_AS_KS_WS排序規(guī)則排序后的
最后一個漢字：

亅阝馬風(fēng)龍齊龜齒鴆齔龕龂齠齦齪龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘

　　上面可以看出：“亅”是所有一筆漢字排序后的最后一個字，“阝”是所有二筆漢字排序后的最后
一個字......等等。
　　但同時也發(fā)現(xiàn)，從第33個漢字“龗(33筆)”后面的筆劃有些亂，不正確。但沒關(guān)系，比“龗”筆劃
多的只有四個漢字，我們手工加上：齾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.codeB.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

　　到此為止，我們可以得到結(jié)果了，比如我們想得到漢字“國”的筆劃：

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
(結(jié)果：漢字“國”筆劃數(shù)為8)

　　上面所有準(zhǔn)備過程，只是為了寫下面這個函數(shù)，這個函數(shù)撇開上面建的所有臨時表和固
定表，為了通用和代碼轉(zhuǎn)移方便，把表tab_hzbh的內(nèi)容寫在語句內(nèi)，然后計算用戶輸入一串
漢字的總筆劃：

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)
--如果非漢字，筆劃當(dāng)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'風(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_WS
order  by  id  ASC)  else  0  end)
set  @str=right(@str,len(@str)-1)
end
return  @n
end

--函數(shù)調(diào)用實例：
select  dbo.fun_getbh('中華人民共和國'),dbo.fun_getbh('中華人民共和國')
　
　　執(zhí)行結(jié)果：筆劃總數(shù)分別為39和46，簡繁體都行。

       當(dāng)然，你也可以把上面“UNION　ALL”內(nèi)的漢字和筆劃改存在固定表內(nèi)，在漢字
列建CLUSTERED  INDEX，列排序規(guī)則設(shè)定為：
　　　  Chinese_PRC_Stroke_CS_AS_KS_WS
這樣速度更快。如果你用的是BIG5碼的操作系統(tǒng)，你得另外生成漢字，方法一樣。
但有一點要記?。哼@些漢字是通過SQL語句Select出來的，不是手工輸入的，更不
是查字典得來的，因為新華字典畢竟不同于UNICODE字符集，查字典的結(jié)果會不正
確。

　　
                   　　　　用排序規(guī)則的特性得到漢字拼音首字母

　　用得到筆劃總數(shù)相同的方法，我們也可以寫出求漢字拼音首字母的函數(shù)。如下：

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

--函數(shù)調(diào)用實例：
select  dbo.fun_getPY('中華人民共和國'),dbo.fun_getPY('中華人民共和國')
結(jié)果都為：ZHRMGHG

　    你若有興趣，也可用相同的方法，擴(kuò)展為得到漢字全拼的函數(shù)，甚至還可以得到全拼的讀
音聲調(diào)，不過全拼分類大多了。得到全拼最好是用對照表，兩萬多漢字搜索速度很快，用對照
表還可以充分利用表的索引。
       排序規(guī)則還有很多其它的巧妙用法。歡迎大家共同探討。

您可能感興趣的文章:

SQLserver排序規(guī)則基本概念探索

標(biāo)簽：營口邯鄲七臺河西寧來賓無錫寶雞汕尾

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《整理一下SQLSERVER的排序規(guī)則》，本文關(guān)鍵詞整理,一下,SQLSERVER,的,排序,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。

濮阳杆衣贸易有限公司

整理一下SQLSERVER的排序規(guī)則