当前位置:  数据库>sqlserver

整理一下SQLSERVER的排序规则

    来源: 互联网  发布时间:2014-09-05

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

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.code0
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

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

    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • jquery默认校验规则整理
  • win7/Windows7系统下载地址搜集整理
  • 请问在linux下面,有没有硬盘整理的功能,或者有什么软件可以实现硬盘整理的功能?
  • redhat/centos 常用信息查看命令整理
  • 磁盘碎片整理工具 Vopt
  • Docker技术使用场景主要特性等相关资源整理
  • 磁盘整理软件 Defraggler
  • 磁盘碎片整理工具 JkDefrag
  • 请教redhat9上如何进行内存整理
  • 开源磁盘整理工具 UltraDefrag
  • 请问前辈们设计source整理工具,使用lex+yacc好还是perl?
  • 请问 GVIM 中有没有可以整理代码的插件?
  • 听说在linux下面不用磁盘整理,是真的吗?那它用的是什么算法啊?请教
  • 磁盘整理工具 JkDefragGUI
  • 如何整理不规范的java源文件?
  • 整理了一下 Linux C 库函数与系统调用,请大家看看,顺便帮忙检查一下
  • linux内核里面的所有头文件,有没有整理过的,请高手指点!谢谢!!!
  • linux里面的所有头文件,有没有整理过的,发一份,谢谢!!!
  • Sql Server 数据库索引整理语句,自动整理数据库索引
  • ORACLE数据库空间整理心得
  • jquery组件使用中遇到的问题整理及解决
  • 做网页经常要注意的常识 整理收集


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3