学步园

CREATE TABLE AWord(
 PY varchar(10),
 ZW nvarchar(10)
) ON [PRIMARY]
GO
--插入数据
insert into aword
select 'A',N'骜'
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'咗'
GO
--建立一个获取拼音首字母的函数
create function f_GetPY
(
 @str nvarchar(2)
) 
returns nvarchar(2)
as 
begin
 set @str = left(@str, 1)
 return (
  case when unicode(@str) between 19968 and 19968+20901 then(
   select top 1 PY+'.' from aword where zw>=@str 
   collate Chinese_PRC_CS_AS_KS_WS order by PY ASC
  ) else @str end
 )
end

select * from crm_customer a order by dbo.f_GetPY(a.linkman)