SQLServer中文排序

--按笔画排序
select ChannelID,ChannelName from dbo.HB_AdChannel order by ChannelName collate chinese_prc_stroke_cs_as_ks_ws

--按拼音排序
select ChannelID,ChannelName+Description from dbo.HB_AdChannel order by ChannelName+Description collate chinese_prc_cs_as_ks_ws



--sql如何让中文名字按 名字第一个字的拼音首字母排序
select isnull(b.py,upper(left(a.ChannelName+a.Description,1))) as py
,a.ChannelName+a.Description as ChannelName
from dbo.HB_AdChannel 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(ChannelName+a.Description,1) between b.sword and b.word
order by py

转载于:https://www.cnblogs.com/wuxiang/archive/2012/08/14/2638224.html