你要那种结果select * from ta order by col desc
select * from ta order by col asc
select * from ta order by rtrim(ltrim(col)) desc
select * from ta order by left(col,1) desc
select * from ta order by left(col,1) desc,substring( col , 3, 10 ) desc
select * from ta order by newid()
select * from ta order by col asc
select * from ta order by rtrim(ltrim(col)) desc
select * from ta order by left(col,1) desc
select * from ta order by left(col,1) desc,substring( col , 3, 10 ) desc
select * from ta order by newid()
declare @t table(s varchar(20))insert @t select '0.5'
union all select '5*1.0'
union all select '1.5'
union all select '2*0.5'
union all select '2*10'
union all select '2*1.5'select
s
from @t
select
s
from @t
order by case when charindex('*',s) = 0
then convert(decimal(10,2),s)
else 100 end,
case when charindex('*',s) = 0 then 0
else convert(decimal(10,2),substring(s,1,charindex('*',s) - 1)) end,
case when charindex('*',s) = 0 then 0
else convert(decimal(10,2),substring(s,charindex('*',s) + 1,len(s))) end
比如存储在数据的顺序为
0.5
2*10
2*0.5
2*1.5
5*1.0
3*3+2*1
1.5
2*5+1*0.5
先根据*号左边的数字从小到大排序,左边比完再按*后的数字大小排序,最后结果为:
0.5
1.5
2*0.5
2*1.5
2*10
5*1.0
2*5+1*0.5
3*3+2*1对于+后的部分不要比较,主要+前的部分按已上面的方法排序。怎么把你的程序再改一下,带+的记录放最后也行放5*1.0前也行。
insert into tb values('0.5')
insert into tb values('2*10')
insert into tb values('2*0.5')
insert into tb values('2*1.5')
insert into tb values('5*1.0')
insert into tb values('1.5')
goselect val from
(
select val , val1 = cast(val as decimal(18,1)) , val2 = 0.00 from tb where charindex('*',val) <= 0
union all
select val , val1 = cast(left(val , charindex('*',val)-1) as decimal(18,2)), val2 = cast(substring(val , charindex('*',val)+1 , len(val)) as decimal(18,2)) from tb where charindex('*',val) > 0
) t
order by val1,val2drop table tb/*
val
----------
0.5
1.5
2*0.5
2*1.5
2*10
5*1.0(所影响的行数为 6 行)
*/
declare @T table(Col varchar(100))
insert @T
select '0.5' union all
select '21*10' union all
select '2*0.5' union all
select '2*1.5' union all
select '5*1.0' union all
select '3*3+2*1' union all
select '1.5' union all
select '2*5+1*0.5'--带+的纪录放5*1.0前
select * from @T order by
--先按*左边的数值排序
cast('1'+substring('1'+Col,1,charindex('*',Col)) as numeric(10,5)),
--再按*和+之间的数值排序
cast(substring(Col,charindex('*',Col)+1,len(Col)-charindex('*',Col)-charindex('+',reverse(Col))) as numeric(10,5))--带+的记录放最后
select * from @T order by
--先将带+的纪录排最后
charindex('+',Col),
--按*左边的数值排序
cast('1'+substring('1'+Col,1,charindex('*',Col)) as numeric(10,5)),
--按*和+之间的数值排序
cast(substring(Col,charindex('*',Col)+1,len(Col)-charindex('*',Col)-charindex('+',reverse(Col))) as numeric(10,5))
then convert(decimal(10,2),s)
else 100 end,
case when charindex('*',s) = 0 then 0
else convert(decimal(10,2),substring(s,1,charindex('*',s) - 1)) end,
case when charindex('*',s) = 0 then 0
else convert(decimal(10,2),substring(s,charindex('*',s) + 1,len(s))) end
---------------------------------------------------------------
谁能给解释一下 order by 子句,我看不懂!