declare @t table (编号 varchar(2),值 varchar(10)) insert into @t select '01','1' union all select '02','2' union all select '03','1.2' union all select '04','2.1' union all select '05','0.99'select * from @t order by len(值),cast(值 as decimal(3,2)) desc /* 编号 值 ---- ---------- 02 2 01 1 04 2.1 03 1.2 05 0.99 */
--try select * from tb order by (case when charindex('.',ltrim(convert(float,值)))>0 then 1 else 0 end),值 desc
declare @t table (编号 varchar(2),值 varchar(10)) insert into @t select '01','1.00' union all select '02','2.00' union all select '03','1.20' union all select '04','2.10' union all select '05','0.99'select * from @t --编号 值 ------ ---------- --02 2.00 --01 1.00 --04 2.10 --03 1.20 --05 0.99 --*/ select * from @t order by SUBSTRING(值,CHARINDEX('.',值)+1,1) , SUBSTRING(值,CHARINDEX('.',值)-1,1) desc 编号 值 ---- ---------- 02 2.00 01 1.00 04 2.10 03 1.20 05 0.99(5 行受影响)
declare @t table (编号 varchar(2),值 varchar(10)) insert into @t select '01','1' union all select '02','2' union all select '03','1.20' union all select '04','2.10' union all select '05','0.99' select * from @t order by (case when charindex('.',值)>0 then 1 else 0 end),值 desc
正在安装sql2008...我表示没有工具,我亚历山大啊
declare @t table (编号 varchar(2),值 varchar(10))
insert into @t
select '01','1' union all
select '02','2' union all
select '03','1.2' union all
select '04','2.1' union all
select '05','0.99'select * from @t order by len(值),cast(值 as decimal(3,2)) desc
/*
编号 值
---- ----------
02 2
01 1
04 2.1
03 1.2
05 0.99
*/
--try
select * from tb order by
(case when charindex('.',ltrim(convert(float,值)))>0 then 1 else 0 end),值 desc
declare @t table (编号 varchar(2),值 varchar(10))
insert into @t
select '01','1.00' union all
select '02','2.00' union all
select '03','1.20' union all
select '04','2.10' union all
select '05','0.99'select * from @t
--编号 值
------ ----------
--02 2.00
--01 1.00
--04 2.10
--03 1.20
--05 0.99
--*/
select * from @t order by SUBSTRING(值,CHARINDEX('.',值)+1,1) , SUBSTRING(值,CHARINDEX('.',值)-1,1) desc
编号 值
---- ----------
02 2.00
01 1.00
04 2.10
03 1.20
05 0.99(5 行受影响)
insert into @t
select '01','1' union all
select '02','2' union all
select '03','1.20' union all
select '04','2.10' union all
select '05','0.99'
select * from @t order by (case when charindex('.',值)>0 then 1 else 0 end),值 desc