问鼎社区_成都问鼎科技官方论坛

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 27450|回复: 0

SQL按拼音字母查询指定字段的做法

[复制链接]
发表于 2012-1-4 11:11:34 | 显示全部楼层 |阅读模式
比如说我们要查到指定字段第一个字是以韵母“L”开头的,我们可以用以下SQL语句进行查询:
   SELECT * FROM 表名 WHERE author 所要查询的字段>='垃' AND 所要查询的字段 <'妈'
   
这个语句就能查询出“所要查询的字段”的第一个字以“L”开头的所要数据。
    这查询方法的依据是某一个韵母在新华字典中的首个汉字和下一个韵母的第一个汉字作为查询的条件,进行查询。如果查询的时候要包含英文字母,可以使用下面的语句:
    SELECT * FROM 表名 WHERE author 所要查询的字段 LIKE 'L'   OR ( 所要查询的字段>='垃' AND 所要查询的字段 <'妈')
  特别说明一下当要查询“Z”的时候,查询的条件是“ >='杂' OR <'坐' ”
   下面是韵母和汉字的对照表:
a: 吖b:巴c: 擦d: 搭e: 鹅f: 发g: 旮
h: 哈i:j: 鸡k: 喀l: 垃m: 妈n: 嗯
o: 哦p: 趴q: 欺
r: 然s: 仨t: 他
u:v:w: 挖
x: 西y: 压z: 杂

这个是我的项目中用到的一个例子,不知道是否是最佳的,若朋友们有更好的方法,还请赐教!
-按26个字母搜索
if(@key<>'')
begin--0
if(@key='A')
begin--1
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='吖' and movename<'巴') or movename like 'A%')
end--1
else if(@key='B')
begin--2
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='巴' and movename<'擦') or movename like 'B%')
end--2
else if(@key='C')
begin--3
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='擦' and movename<'搭') or movename like 'C%')
end--3
else if(@key='D')
begin--4
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='搭' and movename<'鹅') or movename like 'D%')
end--4
else if(@key='E')
begin--5
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='仨' and movename<'他') or movename like 'E%')
end--5
-------------------------
else if(@key='F')
begin--6
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='发' and movename<'旮') or movename like 'F%')
end--6
else if(@key='G')
begin--7
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='旮' and movename<'哈') or movename like 'G%')
end--7
else if(@key='H')
begin--8
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='哈' and movename<'鸡') or movename like 'H%')
end--8
else if(@key='I')
begin--9
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and  movename like 'I%'
end--9
else if(@key='J')
begin--10
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='鸡' and movename<'喀') or movename like 'J%')
end--10
---------------------------------------------------------
else if(@key='K')
begin--11
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='喀' and movename<'垃') or movename like 'K%')
end--11
else if(@key='L')
begin--12
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='垃' and movename<'妈') or movename like 'L%')
end--12
else if(@key='M')
begin--13
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='妈' and movename<'嗯') or movename like 'M%')
end--13
else if(@key='N')
begin--14
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='嗯' and movename<'哦') or movename like 'N%')
end--14
else if(@key='O')
begin--15
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='哦' and movename<'趴') or movename like 'O%')
end--15
---------------------------------------------
else if(@key='P')
begin--16
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='趴' and movename<'欺') or movename like 'P%')
end--16
else if(@key='Q')
begin--17
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='欺' and movename<'然') or movename like 'Q%')
end--17
else if(@key='R')
begin--18
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='然' and movename<'仨') or movename like 'R%')
end--18
else if(@key='S')
begin--19
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='仨' and movename<'他') or movename like 'S%')
end--19
else if(@key='T')
begin--20
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='他' and movename<'挖') or movename like 'T%')
end--20
---------------------------------------------------
else if(@key='U')
begin--21
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and movename like 'U%'
end--21
else if(@key='V')
begin--22
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and movename like 'V%'
end--22
else if(@key='W')
begin--23
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='挖' and movename<'西') or movename like 'W%')
end--23
else if(@key='X')
begin--24
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='西' and movename<'压') or movename like 'X%')
end--24
else if(@key='Y')
begin--25
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='压' and movename<'杂') or movename like 'Y%')
end--25
else if(@key='Z')
begin--26
   select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0) and ((movename>='杂' and movename<'坐') or movename like 'Z%')
end--26
end--0
else
begin--0-1
select a.*,b.MoveKindName from dm_MovesName a left join dm_MoveKinds b on a.MoveKindId=b.MoveKindId where (a.movename like '%'+@movename+'%' or @movename='') and (b.MoveKindParentId=@MoveKindParentId or @MoveKindParentId=0)
end--0-1

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|Archiver|手机版|问鼎社区  

GMT+8, 2020-4-9 11:32 , Processed in 0.415330 second(s), 23 queries .

Powered by Discuz! X3.1

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表