create table tt(col1 varchar(10)) Insert into tt select '1' union all select '2' union all select '3' union all select 'a' union all select 'd3' union all select 's' union all select '4' union all select '5e'select * from ttselect col1 from tt order by case when isnumeric(col1)=0 then 0 else 1 end,col1--結果 col1 ------------------- 5e a d3 s 1 2 3 4
select col1 from 表 where isnumeric(col1)=1 order by col1--结果col1 ---------- 1 2 3 4
或者这样 create table tt(col1 varchar(10)) Insert into tt select '1' union all select '2' union all select '3' union all select 'a' union all select 'd3' union all select 's' union all select '4' union all select '5e'select col1 from tt order by (case when isnumeric(col1)=1 then col1 else 0 end) drop table tt--运行结果:col1 ---------- a d3 s 5e 1 2 3 4
Insert into tt
select '1'
union all select '2'
union all select '3'
union all select 'a'
union all select 'd3'
union all select 's'
union all select '4'
union all select '5e'select * from ttselect col1 from tt order by case when isnumeric(col1)=0 then 0 else 1 end,col1--結果
col1
-------------------
5e
a
d3
s
1
2
3
4
where isnumeric(col1)=1
order by col1--结果col1
----------
1
2
3
4
create table tt(col1 varchar(10))
Insert into tt
select '1'
union all select '2'
union all select '3'
union all select 'a'
union all select 'd3'
union all select 's'
union all select '4'
union all select '5e'select col1 from tt
order by (case when isnumeric(col1)=1 then col1 else 0 end) drop table tt--运行结果:col1
----------
a
d3
s
5e
1
2
3
4