SQL按汉语拼音首字母排序

浏览数：103 / 时间：2015年06月12日

以常用到的省的数据表（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