--取拼音首字母的用户定义函数
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go--生成测试数据
create table #T(position_id int,user_name varchar(100))
insert into #T select 1 ,rtrim('Ashiley')
insert into #T select 2 ,rtrim('敖包 ')
insert into #T select 3 ,rtrim('敖包 ')
insert into #T select 4 ,rtrim('Bird ')
insert into #T select 5 ,rtrim('A艾力 ')
insert into #T select 7 ,rtrim('北京 ')
insert into #T select 8 ,rtrim('笔笔 ')
insert into #T select 9 ,rtrim('白work ')
insert into #T select 10,rtrim('1360139')
insert into #T select 11,rtrim('爸爸 ')
insert into #T select 12,rtrim('敖广 ')
insert into #T select 13,rtrim('138126 ')
insert into #T select 15,rtrim('安佳琪 ')
insert into #T select 16,rtrim('busy ')
insert into #T select 19,rtrim('139111 ')
insert into #T select 28,rtrim('baby ')--执行查询
select * from #T
order by
case when isnumeric(left(user_name,1))=1 then 2 else 1 end, --根据字符串首字符是否为数字进行排序
dbo.f_GetPy(left(user_name,1)), --根据拼音首字母进行排序
case when left(user_name,1)=dbo.f_GetPy(left(user_name,1))
then 1
else 2 end, --根据中英文进行排序
position_id --根据position_id进行排序drop table #T
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go--生成测试数据
create table #T(position_id int,user_name varchar(100))
insert into #T select 1 ,rtrim('Ashiley')
insert into #T select 2 ,rtrim('敖包 ')
insert into #T select 3 ,rtrim('敖包 ')
insert into #T select 4 ,rtrim('Bird ')
insert into #T select 5 ,rtrim('A艾力 ')
insert into #T select 7 ,rtrim('北京 ')
insert into #T select 8 ,rtrim('笔笔 ')
insert into #T select 9 ,rtrim('白work ')
insert into #T select 10,rtrim('1360139')
insert into #T select 11,rtrim('爸爸 ')
insert into #T select 12,rtrim('敖广 ')
insert into #T select 13,rtrim('138126 ')
insert into #T select 15,rtrim('安佳琪 ')
insert into #T select 16,rtrim('busy ')
insert into #T select 19,rtrim('139111 ')
insert into #T select 28,rtrim('baby ')--执行查询
select * from #T
order by
case when isnumeric(left(user_name,1))=1 then 2 else 1 end, --根据字符串首字符是否为数字进行排序
dbo.f_GetPy(left(user_name,1)), --根据拼音首字母进行排序
case when left(user_name,1)=dbo.f_GetPy(left(user_name,1))
then 1
else 2 end, --根据中英文进行排序
position_id --根据position_id进行排序drop table #T
/*
1 Ashiley
5 A艾力
2 敖包
3 敖包
12 敖广
15 安佳琪
4 Bird
16 busy
28 baby
7 北京
8 笔笔
9 白work
11 爸爸
10 1360139
13 138126
19 139111
*/
--------------------------------------------------------------------------------------------------------------
select * from #T
order by
case when isnumeric(left(user_name,1))=1 then 2 else 1 end, --根据字符串首字符是否为数字进行排序
dbo.f_GetPy(left(user_name,1)), --根据拼音首字母进行排序
case when left(user_name,1)=dbo.f_GetPy(left(user_name,1))
then 1
else 2 end, --根据中英文进行排序
position_id
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'这句SQL看不懂是什么意思,请您讲解一下,非常感谢!
2、INSERT TestTab VALUES (1, N'A', N'a'),
'吖','A' ;N'A'不明白这种写法是什么意思?
insert into #T select 1 ,rtrim('Ashiley')
insert into #T select 2 ,rtrim('敖包 ')
insert into #T select 3 ,rtrim('敖包 ')
insert into #T select 4 ,rtrim('Bird ')
insert into #T select 5 ,rtrim('A艾力 ')
insert into #T select 7 ,rtrim('北京 ')
insert into #T select 8 ,rtrim('笔笔 ')
insert into #T select 9 ,rtrim('白work ')
insert into #T select 10,rtrim('1360139')
insert into #T select 11,rtrim('爸爸 ')
insert into #T select 12,rtrim('敖广 ')
insert into #T select 13,rtrim('138126 ')
insert into #T select 15,rtrim('安佳琪 ')
insert into #T select 16,rtrim('busy ')
insert into #T select 19,rtrim('139111 ')
insert into #T select 28,rtrim('baby ')
select * from #T order by user_name ,position_id
这个应该很简单吧!