SQL按汉语拼音首字母排序

最新推荐文章于 2020-05-13 14:52:47 发布

转载最新推荐文章于 2020-05-13 14:52:47 发布 · 471 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Chaser-Eagle/p/3684815.html

文章标签：

#python

本文介绍了一种利用SQL的LEFT OUTER JOIN结合子查询来实现数据匹配的技巧，通过具体示例展示了如何根据省份名称的首字母进行对应拼音缩写的匹配，适用于需要进行字符或字符串匹配的场景。

以常用到的省的数据表（province）为例，其中name字段为省的名称，SQL语句如下：

 1 select   isnull(b.py,upper(left(a.name,1)))     as   py  
 2   ,a.name  
 3   from   province
 4   a  
 5   left   outer   join   (  
 6   select   'A'   as   PY,N'驁'   as   word,   N'啊'   as   sword  
 7   union   select   'B',N'簿',N'驁'  
 8   union   select   'C',N'錯',N'簿'  
 9   union   select   'D',N'鵽',N'錯'  
10   union   select   'E',N'樲',N'鵽'  
11   union   select   'F',N'鰒',N'樲'  
12   union   select   'G',N'腂',N'鰒'  
13   union   select   'H',N'夻',N'腂'  
14   union   select   'J',N'攈',N'夻'  
15   union   select   'K',N'穒',N'攈'  
16   union   select   'L',N'鱳',N'穒'  
17   union   select   'M',N'旀',N'鱳'  
18   union   select   'N',N'桛',N'旀'  
19   union   select   'O',N'漚',N'桛'  
20   union   select   'P',N'曝',N'漚'  
21   union   select   'Q',N'囕',N'曝'  
22   union   select   'R',N'鶸',N'囕'  
23   union   select   'S',N'蜶',N'鶸'  
24   union   select   'T',N'籜',N'蜶'  
25   union   select   'W',N'鶩',N'籜'  
26   union   select   'X',N'鑂',N'鶩'  
27   union   select   'Y',N'韻',N'鑂'  
28   union   select   'Z',N'咗',N'韻'  
29   )   b   on   left(name,1)   between   b.sword   and   b.word order by name