SQL Server 整理一下的排序规则
2022-11-12 09:53:06
内容摘要
这篇文章主要为大家详细介绍了SQL Server 整理一下的排序规则，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！SQL SERVER的排序规则平时
文章正文
这篇文章主要为大家详细介绍了SQL Server 整理一下的排序规则，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
SQL SERVER的排序规则平时使用不是很多，也许不少初学者还比较陌生，但有一个错误大家应是经常碰到:SQLSERVER数据库，在跨库多表连接查询时，若两数据库默认字符集不同，系统就会返回这样的错误：“无法解决equalto操作的排序规则冲突。”一.错误分析： 这个错误是因为排序规则不一致造成的，我们做个测试，比如：createtable#t1(namevarchar(20)collateAlbanian_CI_AI_WS,valueint)createtable#t2(namevarchar(20)collateChinese_PRC_CI_AI_WS,valueint)表建好后，执行连接查询：select*from#t1Ainnerjoin#t2BonA.name=B.name这样，错误就出现了：服务器:消息446，级别16，状态9，行1无法解决equalto操作的排序规则冲突。 要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就不再出现了。语句这样写：select*from#t1Ainnerjoin#t2BonA.name=B.namecollateChinese_PRC_CI_AI_WS二.排序规则简介：什么叫排序规则呢？MS是这样描述的："在MicrosoftSQLServer2000中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。" 在查询分析器内执行下面语句，可以得到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区分 区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，比较还将重音不同的字母视为不等。区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项三.排序规则的应用： SQLSERVER提供了大量的WINDOWS和SQLSERVER专用的排序规则，但它的应用往往被开发人员所忽略。其实它在实践中大有用处。 例1:让表NAME列的内容按拼音排序：createtable#t(idint,namevarchar(20))insert#tselect1,'中'unionallselect2,'国'unionallselect3,'人'unionallselect4,'阿'select*from#torderbynamecollateChinese_PRC_CS_AS_KS_WSdroptable#t/*结果：idname-------------------------------4阿2国3人1中*/ 例2：让表NAME列的内容按姓氏笔划排序：createtable#t(idint,namevarchar(20))insert#tselect1,'三'unionallselect2,'乙'unionallselect3,'二'unionallselect4,'一'unionallselect5,'十'select*from#torderbynamecollateChinese_PRC_Stroke_CS_AS_KS_WSdroptable#t/*结果：idname-------------------------------4一2乙3二5十1三*/四.在实践中排序规则应用的扩展 SQLSERVER汉字排序规则可以按拼音、笔划等排序，那么我们如何利用这种功能来处理汉字的一些难题呢？我现在举个例子： 用排序规则的特性计算汉字笔划 要计算汉字笔划，我们得先做准备工作，我们知道，WINDOWS多国汉字，UNICODE目前收录汉字共20902个。简体GBK码汉字UNICODE值从19968开始。 首先，我们先用SQLSERVER方法得到所有汉字，不用字典，我们简单利用SQL语句就可以得到：selecttop20902code=identity(int,19968,1)into#tfromsyscolumnsa,syscolumnsb再用以下语句，我们就得到所有汉字，它是按UNICODE值排序的： selectcode,nchar(code)asCNWordfrom#t 然后，我们用Select语句，让它按笔划排序。selectcode,nchar(code)asCNWordfrom#torderbynchar(code)collateChinese_PRC_Stroke_CS_AS_KS_WS,code结果：codeCNWord-----------------19968一20008丨20022丶20031丿20032乀20033乁20057乙20058乚20059乛20101亅19969丁.......... 从上面的结果，我们可以清楚的看到，一笔的汉字，code是从19968到20101，从小到大排，但到了二笔汉字的第一个字“丁”，CODE为19969，就不按顺序而重新开始了。有了这结果，我们就可以轻松的用SQL语句得到每种笔划汉字归类的第一个或最后一个汉字。下面用语句得到最后一个汉字：createtable#t1(idintidentity,codeint,cnwordnvarchar(2))insert#t1(code,cnword)selectcode,nchar(code)asCNWordfrom#torderbynchar(code)collateChinese_PRC_Stroke_CS_AS_KS_WS,codeselectA.cnwordfrom#t1Aleftjoin#t1BonA.id=B.id-1andA.code<B.codewhereB.codeisnullorderbyA.id得到36个汉字，每个汉字都是每种笔划数按Chinese_PRC_Stroke_CS_AS_KS_WS排序规则排序后的最后一个汉字：亅阝马风龙齐龟齿鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘 上面可以看出：“亅”是所有一笔汉字排序后的最后一个字，“阝”是所有二笔汉字排序后的最后一个字......等等。 但同时也发现，从第33个汉字“龗(33笔)”后面的笔划有些乱，不正确。但没关系，比“龗”笔划多的只有四个汉字，我们手工加上：齾35笔，齉36笔，靐39笔，龘64笔建汉字笔划表（TAB_HZBH）：createtabletab_hzbh(idintidentity,cnwordnchar(1))--先插入前33个汉字inserttab_hzbhselecttop33A.cnwordfrom#t1Aleftjoin#t1BonA.id=B.id-1andA.code<B.codewhereB.codeisnullorderbyA.id--再加最后四个汉字setidentity_inserttab_hzbhongoinserttab_hzbh(id,cnword) select35,N'齾'unionallselect36,N'齉'unionallselect39,N'靐'unionallselect64,N'龘'gosetidentity_inserttab_hzbhoffgo 到此为止，我们可以得到结果了，比如我们想得到汉字“国”的笔划：declare@anchar(1)set@a='国'selecttop1idfromtab_hzbhwherecnword>=@acollateChinese_PRC_Stroke_CS_AS_KS_WSorderbyidid-----------8(结果：汉字“国”笔划数为8) 上面所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固定表，为了通用和代码转移方便，把表tab_hzbh的内容写在语句内，然后计算用户输入一串汉字的总笔划：createfunctionfun_getbh(@strnvarchar(4000))returnsintasbegindeclare@wordnchar(1),@nintset@n=0whilelen(@str)>0beginset@word=left(@str,1)--如果非汉字，笔划当0计set@n=@n+(casewhenunicode(@word)between19968and19968+20901then(selecttop1idfrom(select1asid,N'亅'aswordunionallselect2,N'阝'unionallselect3,N'马'unionallselect4,N'风'unionallselect5,N'龙'unionallselect6,N'齐'unionallselect7,N'龟'unionallselect8,N'齿'unionallselect9,N'鸩'unionallselect10,N'龀'unionallselect11,N'龛'unionallselect12,N'龂'unionallselect13,N'龆'unionallselect14,N'龈'unionallselect15,N'龊'unionallselect16,N'龍'unionallselect17,N'龠'unionallselect18,N'龎'unionallselect19,N'龐'unionallselect20,N'龑'unionallselect21,N'龡'unionallselect22,N'龢'unionallselect23,N'龝'unionallselect24,N'齹'unionallselect25,N'龣'unionallselect26,N'龥'unionallselect27,N'齈'unionallselect28,N'龞'unionallselect29,N'麷'unionallselect30,N'鸞'unionallselect31,N'麣'unionallselect32,N'龖'unionallselect33,N'龗'unionallselect35,N'齾'unionallselect36,N'齉'unionallselect39,N'靐'unionallselect64,N'龘')Twhereword>=@wordcollateChinese_PRC_Stroke_CS_AS_KS_WSorderbyidASC)else0end)set@str=right(@str,len(@str)-1)endreturn@nend--函数调用实例：selectdbo.fun_getbh('中华人民共和国'),dbo.fun_getbh('中華人民共和國') 执行结果：笔划总数分别为39和46，简繁体都行。当然，你也可以把上面“UNION ALL”内的汉字和笔划改存在固定表内，在汉字列建CLUSTEREDINDEX，列排序规则设定为： Chinese_PRC_Stroke_CS_AS_KS_WS这样速度更快。如果你用的是BIG5码的操作系统，你得另外生成汉字，方法一样。但有一点要记住：这些汉字是通过SQL语句Select出来的，不是手工输入的，更不是查字典得来的，因为新华字典毕竟不同于UNICODE字符集，查字典的结果会不正确。 用排序规则的特性得到汉字拼音首字母 用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：createfunctionfun_getPY(@strnvarchar(4000))returnsnvarchar(4000)asbegindeclare@wordnchar(1),@PYnvarchar(4000)set@PY=''whilelen(@str)>0beginset@word=left(@str,1)--如果非汉字字符，返回原字符set@PY=@PY+(casewhenunicode(@word)between19968and19968+20901then(selecttop1PYfrom(select'A'asPY,N'驁'aswordunionallselect'B',N'簿'unionallselect'C',N'錯'unionallselect'D',N'鵽'unionallselect'E',N'樲'unionallselect'F',N'鰒'unionallselect'G',N'腂'unionallselect'H',N'夻'unionallselect'J',N'攈'unionallselect'K',N'穒'unionallselect'L',N'鱳'unionallselect'M',N'旀'unionallselect'N',N'桛'unionallselect'O',N'漚'unionallselect'P',N'曝'unionallselect'Q',N'囕'unionallselect'R',N'鶸'unionallselect'S',N'蜶'unionallselect'T',N'籜'unionallselect'W',N'鶩'unionallselect'X',N'鑂'unionallselect'Y',N'韻'unionallselect'Z',N'咗')Twhereword>=@wordcollateChinese_PRC_CS_AS_KS_WSorderbyPYASC)else@wordend)set@str=right(@str,len(@str)-1)endreturn@PYend--函数调用实例：selectdbo.fun_getPY('中华人民共和国'),dbo.fun_getPY('中華人民共和國')结果都为：ZHRMGHG 你若有兴趣，也可用相同的方法，扩展为得到汉字全拼的函数，甚至还可以得到全拼的读音声调，不过全拼分类大多了。得到全拼最好是用对照表，两万多汉字搜索速度很快，用对照表还可以充分利用表的索引。排序规则还有很多其它的巧妙用法。欢迎大家共同探讨。注：关于SQL Server 整理一下的排序规则的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
