SQL按汉语拼音首字母排序

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

其运行结果如下图：

转载于:https://www.cnblogs.com/Chaser-Eagle/p/3684815.html