1:建立函数
create function GetFirstPY(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
endgo2:
select * from yourtable order by dbo.GetFirstPY(field1)
create function GetFirstPY(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @n = @n +1,
@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn =
'吖' union all select
'八' union all select
'嚓' union all select
'咑' union all select
'妸' union all select
'发' union all select
'旮' union all select
'铪' union all select
'丌' union all select --because have no 'i'
'丌' union all select
'咔' union all select
'垃' union all select
'嘸' union all select
'拏' union all select
'噢' union all select
'妑' union all select
'七' union all select
'呥' union all select
'仨' union all select
'他' union all select
'屲' union all select --no 'u'
'屲' union all select --no 'v'
'屲' union all select
'夕' union all select
'丫' union all select
'帀' union all select @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
endgo2:
select * from yourtable order by dbo.GetFirstPY(field1)
insert into @tb
select '一'
union all select '二'
union all select '三'
union all select '四'
union all select '五'
union all select '六'
union all select '七'
union all select '八'
union all select '九'
union all select '十'--直接排序
select * from @tb
order by aa --按拼音排序
select * from @tb
order by aa COLLATE Latin1_General_BIN
aa
----------
八
二
九
六
七
三
十
四
五
一(所影响的行数为 10 行)aa
----------
一
二
三
四
五
六
七
八
九
十(所影响的行数为 10 行)
直接表里面用上面這一行好像不行喲?
Line 1: Incorrect syntax near 'Chinese_PRC_CI_AS'.