create table #a(id varchar(20))insert into #a select '6.5*100' union select '8' union select '10*1' union select '10*2' union select '10*10' union select '10*25' union select '10*11'select * from #a order by case when charindex('*',id)>0 then cast(left(id,charindex('*',id)-1) as decimal(10,2)) else cast(id as int) end, cast(substring(id,charindex('*',id)+1,len(id)) as int) desc/* 6.5*100 8 10*25 10*11 10*10 10*2 10*1 */
USE test GO-->生成表tbif object_id('tb') is not null drop table tb Go Create table tb([col1] nvarchar(8)) Insert into tb Select N'10*20' Union all Select N'10*15' Union all Select N'10*10' Union all Select N'10*9' Union all Select N'10*8' Union all Select N'8' Union all Select N'6.5*1500' SELECT * FROM tb ORDER BY CONVERT(NUMERIC(38,18),LEFT(col1,PATINDEX('%[^0-9.]%',col1+'*')-1)) ASC ,CONVERT(NUMERIC(38,18),STUFF(col1,1,PATINDEX('%[^0-9.]%',col1),'')) DESC/* col1 -------- 6.5*1500 8 10*20 10*15 10*10 10*9 10*8 */ Go
create table #a(id varchar(20))insert into #a
select '6.5*100'
union
select '8'
union
select '10*1'
union
select '10*2'
union
select '10*10'
union
select '10*25'
union
select '10*11'select * from #a
order by case when charindex('*',id)>0 then cast(left(id,charindex('*',id)-1) as decimal(10,2))
else cast(id as int) end,
cast(substring(id,charindex('*',id)+1,len(id)) as int) desc/*
6.5*100
8
10*25
10*11
10*10
10*2
10*1
*/
GO-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([col1] nvarchar(8))
Insert into tb
Select N'10*20'
Union all Select N'10*15'
Union all Select N'10*10'
Union all Select N'10*9'
Union all Select N'10*8'
Union all Select N'8'
Union all Select N'6.5*1500'
SELECT
*
FROM tb
ORDER BY
CONVERT(NUMERIC(38,18),LEFT(col1,PATINDEX('%[^0-9.]%',col1+'*')-1)) ASC
,CONVERT(NUMERIC(38,18),STUFF(col1,1,PATINDEX('%[^0-9.]%',col1),'')) DESC/*
col1
--------
6.5*1500
8
10*20
10*15
10*10
10*9
10*8
*/
Go