asp中sql语句怎么能按姓名的首字母排序

发布网友发布时间：2022-04-07 23:47

共2个回答

懂视网时间：2022-04-08 04:08

oracle ：

在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序，系统的默认排序方式为拼音排序

举例如下：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
//按照笔划排序
select * from dept order by nlssort(name,‘NLS_SORT=SCHINESE_STROKE_M‘);
//按照部首排序
select * from dept order by nlssort(name,‘NLS_SORT=SCHINESE_RADICAL_M‘);
//按照拼音排序，此为系统的默认排序方式
select * from dept order by nlssort(name,‘NLS_SORT=SCHINESE_PINYIN_M‘);

sqlserver

select * from table order by name collate Chinese_PRC_CS_AS_KS_WS

sql语句按照汉字拼音首字母排序

标签：

热心网友时间：2022-04-08 01:16

sql中按姓名拼音排序 select isnull(b.py,upper(left(a.username,1))) as py
,a.username
from (
select 'a3' as username
union select UserName from UserTable --修改这里的姓名列~
)
a
left outer join (
select 'A' as PY,N'骜' as word, N'啊' as sword
union select 'B',N'簿',N'骜'
union select 'C',N'错',N'簿'
union select 'D',N'鵽',N'错'
union select 'E',N'樲',N'鵽'
union select 'F',N'鳆',N'樲'
union select 'G',N'腂',N'鳆'
union select 'H',N'夻',N'腂'
union select 'J',N'攈',N'夻'
union select 'K',N'穒',N'攈'
union select 'L',N'鱳',N'穒'
union select 'M',N'旀',N'鱳'
union select 'N',N'桛',N'旀'
union select 'O',N'沤',N'桛'
union select 'P',N'曝',N'沤'
union select 'Q',N'囕',N'曝'
union select 'R',N'鶸',N'囕'
union select 'S',N'蜶',N'鶸'
union select 'T',N'箨',N'蜶'
union select 'W',N'鹜',N'箨'
union select 'X',N'鑂',N'鹜'
union select 'Y',N'韵',N'鑂'
union select 'Z',N'咗',N'韵'
) b on left(username,1) between b.sword and b.word
where UserName<>'a3' and py='A' --这里的and py='A'是查询以A拼音开头的姓名，你可以改成其他的，或者直接去掉就是查询所有的~
order by py