SQL排序按 名称排序字母汉字混排

Question

我现有一字段值为
name
------------------------
BBB
CCC
阿阿
查查
AAA
拔拔
怎么样排序可以实现
name
--------------
AAA
阿阿
BBB
拔拔
CCC
查查

Answer 1

--首先准备一个拼音表
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
GO
--然后查询排序
select * from 表 order by dbo.f_GetPY(字段)