---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(18)) insert [tb] select 'zbT}-&1:#BS\' union all select 'Zd}k7;4`5_?k' union all select 'zecUZl\Z4%*z' union all select 'ZFhIC/c79pim' union all select 'zg^TfNIR/P|@' union all select 'zJ>1n,^6Z+r;' union all select 'ZktVpST:#Kg[' union all select 'ZL!Yot[7\[?b' union all select 'zo-tFH|lOrVH' union all select 'ZPUEBf_m(sY:' union all select 'ZQeOFm*t!mu8' union all select 'Zv,>g3o4rPsF' union all select 'Zxa_fGuAy <$y'
---查询--- select * from [tb] order by ascii(left(id,1))---结果--- id ------------------ Zd}k7;4`5_?k ZFhIC/c79pim ZPUEBf_m(sY: ZQeOFm*t!mu8 Zv,>g3o4rPsF Zxa_fGuAy <$y ZktVpST:#Kg[ ZL!Yot[7\[?b zo-tFH|lOrVH zbT}-&1:#BS\ zg^TfNIR/P|@ zJ>1n,^6Z+r; zecUZl\Z4%*z(所影响的行数为 13 行)
create table tb(col varchar(20)) insert into tb values('zbT}-&1:#BS\') insert into tb values('Zd}k7;4`5_?k') insert into tb values('zecUZl\Z4%*z') insert into tb values('ZFhIC/c79pim') insert into tb values('zg^TfNIR/P|@') insert into tb values('zJ>1n,^6Z+r;') insert into tb values('ZktVpST:#Kg[') insert into tb values('ZL!Yot[7\[?b') insert into tb values('zo-tFH|lOrVH') insert into tb values('ZPUEBf_m(sY:') insert into tb values('ZQeOFm*t!mu8') insert into tb values('Zv,>g3o4rPsF') insert into tb values('Zxa_fGuAy <$y') goselect * from tb order by left(col,1) COLLATE Chinese_PRC_CS_AS descdrop table tb/* col -------------------- Zd}k7;4`5_?k ZFhIC/c79pim ZktVpST:#Kg[ ZL!Yot[7\[?b ZPUEBf_m(sY: ZQeOFm*t!mu8 Zv,>g3o4rPsF Zxa_fGuAy <$y zo-tFH|lOrVH zg^TfNIR/P|@ zJ>1n,^6Z+r; zecUZl\Z4%*z zbT}-&1:#BS\(所影响的行数为 13 行)*/
left 1,left 2,……不麻烦,累……
谢谢 但是我是12位都需要按照字母大小写区分排序,能否这样 select * from [tb] order by ascii(left(id,12))
declare @a table(col varchar(20)) insert into @a values('zbT}-&1:#BS\') insert into @a values('Zd}k7;4`5_?k') insert into @a values('zecUZl\Z4%*z') insert into @a values('ZFhIC/c79pim') insert into @a values('zg^TfNIR/P|@') insert into @a values('zJ>1n,^6Z+r;') insert into @a values('ZktVpST:#Kg[') insert into @a values('ZL!Yot[7\[?b') insert into @a values('zo-tFH|lOrVH') insert into @a values('ZPUEBf_m(sY:') insert into @a values('ZQeOFm*t!mu8') insert into @a values('Zv,>g3o4rPsF') insert into @a values('Zxa_fGuAy <$y')select * from @a order by col COLLATE Chinese_PRC_BIN --result /*col -------------------- ZFhIC/c79pim ZL!Yot[7\[?b ZPUEBf_m(sY: ZQeOFm*t!mu8 Zd}k7;4`5_?k ZktVpST:#Kg[ Zv,>g3o4rPsF Zxa_fGuAy <$y zJ>1n,^6Z+r; zbT}-&1:#BS\ zecUZl\Z4%*z zg^TfNIR/P|@ zo-tFH|lOrVH*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(18))
insert [tb]
select 'zbT}-&1:#BS\' union all
select 'Zd}k7;4`5_?k' union all
select 'zecUZl\Z4%*z' union all
select 'ZFhIC/c79pim' union all
select 'zg^TfNIR/P|@' union all
select 'zJ>1n,^6Z+r;' union all
select 'ZktVpST:#Kg[' union all
select 'ZL!Yot[7\[?b' union all
select 'zo-tFH|lOrVH' union all
select 'ZPUEBf_m(sY:' union all
select 'ZQeOFm*t!mu8' union all
select 'Zv,>g3o4rPsF' union all
select 'Zxa_fGuAy <$y'
---查询---
select *
from [tb]
order by ascii(left(id,1))---结果---
id
------------------
Zd}k7;4`5_?k
ZFhIC/c79pim
ZPUEBf_m(sY:
ZQeOFm*t!mu8
Zv,>g3o4rPsF
Zxa_fGuAy <$y
ZktVpST:#Kg[
ZL!Yot[7\[?b
zo-tFH|lOrVH
zbT}-&1:#BS\
zg^TfNIR/P|@
zJ>1n,^6Z+r;
zecUZl\Z4%*z(所影响的行数为 13 行)
insert into tb values('zbT}-&1:#BS\')
insert into tb values('Zd}k7;4`5_?k')
insert into tb values('zecUZl\Z4%*z')
insert into tb values('ZFhIC/c79pim')
insert into tb values('zg^TfNIR/P|@')
insert into tb values('zJ>1n,^6Z+r;')
insert into tb values('ZktVpST:#Kg[')
insert into tb values('ZL!Yot[7\[?b')
insert into tb values('zo-tFH|lOrVH')
insert into tb values('ZPUEBf_m(sY:')
insert into tb values('ZQeOFm*t!mu8')
insert into tb values('Zv,>g3o4rPsF')
insert into tb values('Zxa_fGuAy <$y')
goselect * from tb order by left(col,1) COLLATE Chinese_PRC_CS_AS descdrop table tb/*
col
--------------------
Zd}k7;4`5_?k
ZFhIC/c79pim
ZktVpST:#Kg[
ZL!Yot[7\[?b
ZPUEBf_m(sY:
ZQeOFm*t!mu8
Zv,>g3o4rPsF
Zxa_fGuAy <$y
zo-tFH|lOrVH
zg^TfNIR/P|@
zJ>1n,^6Z+r;
zecUZl\Z4%*z
zbT}-&1:#BS\(所影响的行数为 13 行)*/
left 1,left 2,……不麻烦,累……
但是我是12位都需要按照字母大小写区分排序,能否这样
select *
from [tb]
order by ascii(left(id,12))
insert into @a values('zbT}-&1:#BS\')
insert into @a values('Zd}k7;4`5_?k')
insert into @a values('zecUZl\Z4%*z')
insert into @a values('ZFhIC/c79pim')
insert into @a values('zg^TfNIR/P|@')
insert into @a values('zJ>1n,^6Z+r;')
insert into @a values('ZktVpST:#Kg[')
insert into @a values('ZL!Yot[7\[?b')
insert into @a values('zo-tFH|lOrVH')
insert into @a values('ZPUEBf_m(sY:')
insert into @a values('ZQeOFm*t!mu8')
insert into @a values('Zv,>g3o4rPsF')
insert into @a values('Zxa_fGuAy <$y')select * from @a order by col COLLATE Chinese_PRC_BIN --result
/*col
--------------------
ZFhIC/c79pim
ZL!Yot[7\[?b
ZPUEBf_m(sY:
ZQeOFm*t!mu8
Zd}k7;4`5_?k
ZktVpST:#Kg[
Zv,>g3o4rPsF
Zxa_fGuAy <$y
zJ>1n,^6Z+r;
zbT}-&1:#BS\
zecUZl\Z4%*z
zg^TfNIR/P|@
zo-tFH|lOrVH*/