| 導購 | 订阅 | 在线投稿
分享
 
 
 

講解SQL 2000的Collection排序規則

來源:互聯網  2008-06-01 02:15:15  評論

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 中,字符串的物理存儲由排序規則控制。排序規則指定表示每個字符的位模式以及存儲和比較字符所使用的規則。"在查詢分析器內執行下面語句,可以得到SQLSERVER支持的所有排序規則。

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,簡繁體都行。

當然,你也可以把上面「UNIONALL」內的漢字和筆劃改存在固定表內,在漢字列建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

大家如果有興趣,可以使用相同的方法,擴展得到漢字全拼的函數,甚至你還可以得到全拼的讀音聲調,不過全拼分類大多了。得到全拼最好還是用對照表,兩萬多漢字搜索速度顯然很快,另外,用對照表還可以充分利用表的索引。

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 大家如果有興趣,可以使用相同的方法,擴展得到漢字全拼的函數,甚至你還可以得到全拼的讀音聲調,不過全拼分類大多了。得到全拼最好還是用對照表,兩萬多漢字搜索速度顯然很快,另外,用對照表還可以充分利用表的索引。
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
王朝網路微信公眾號
微信掃碼關註本站公眾號 wangchaonetcn
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有