select * from tb order by case when patindex('%[A-Z]%',Jyouhou)>0 then 2 else 1 end, Jyouhou
SELECT Jyouhou FROM #tb ORDER BY CASE WHEN ISNUMERIC(Jyouhou)=1 THEN CAST(Jyouhou AS float ) END desc
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-08-26 16:00:45 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Jyouhou] varchar(6)) insert [tb] select '0.6' union all select '0.65' union all select '0.7' union all select '0.7X54' union all select 'CCCC' union all select '1.4' union all select '1.0' union all select '1.2' union all select '1.6' --------------开始查询--------------------------select * from tb order by case when patindex('%[A-Z]%',Jyouhou)>0 then 1 else 0 end, Jyouhou ----------------结果---------------------------- /*Jyouhou ------- 0.6 0.65 0.7 1.0 1.2 1.4 1.6 0.7X54 CCCC(9 行受影响)*/
SELECT Jyouhou FROM tb ORDER BY CASE WHEN ISNUMERIC(Jyouhou)=1 THEN 1 else 2 END 楼主的写法改一下也可以
--> 测试数据: @tb declare @tb table (Jyouhou varchar(6)) insert into @tb select '0.6' union all select '0.65' union all select '0.7' union all select '0.7X54' union all select 'CCCC' union all select '1.4' union all select '1.0' union all select '1.2' union all select '1.6'select * from @tb order by case when patindex('%[^0-9.]%',jyouhou)>0 then 2 else 1 end,jyouhou
from tb
order by
case when patindex('%[A-Z]%',Jyouhou)>0 then 2 else 1 end,
Jyouhou
SELECT Jyouhou FROM #tb
ORDER BY CASE WHEN ISNUMERIC(Jyouhou)=1 THEN CAST(Jyouhou AS float ) END desc
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-26 16:00:45
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Jyouhou] varchar(6))
insert [tb]
select '0.6' union all
select '0.65' union all
select '0.7' union all
select '0.7X54' union all
select 'CCCC' union all
select '1.4' union all
select '1.0' union all
select '1.2' union all
select '1.6'
--------------开始查询--------------------------select
*
from
tb
order by
case when patindex('%[A-Z]%',Jyouhou)>0 then 1 else 0 end,
Jyouhou
----------------结果----------------------------
/*Jyouhou
-------
0.6
0.65
0.7
1.0
1.2
1.4
1.6
0.7X54
CCCC(9 行受影响)*/
ORDER BY CASE WHEN ISNUMERIC(Jyouhou)=1 THEN 1 else 2 END
楼主的写法改一下也可以
declare @tb table (Jyouhou varchar(6))
insert into @tb
select '0.6' union all
select '0.65' union all
select '0.7' union all
select '0.7X54' union all
select 'CCCC' union all
select '1.4' union all
select '1.0' union all
select '1.2' union all
select '1.6'select * from @tb order by case when patindex('%[^0-9.]%',jyouhou)>0 then 2 else 1 end,jyouhou