declare @t table(col1 varchar(20),col2 varchar(20))insert @t select 'cp','12p'
union all select 'cp','20p'
union all select 'cp','20p'
union all select 'cp','2p'
union all select 'cp','6p'
union all select 'fp','new 12p'
union all select 'qt','12p'select
*
from @t
order by col1,case when isnumeric(replace(col2,'p','')) = 1 then convert(int,replace(col2,'p','')) else 9999999 end/**
col1 col2
------------
cp 2p
cp 6p
cp 12p
cp 20p
cp 20p
fp new 12p
qt 12p
**/
/*CP 12P
CP 20P
CP 20P
CP 2P
CP 6P
FP-NEW 12P
QT 12P
-----------
CP 2P
CP 6P
CP 12P
CP 20P
CP 20P
FP-NEW 12P
QT 12P
*/
--该方法只适用于第二个字段,最后一位为非数字字符,等待其他高手解决
declare @tb table
(code varchar(8),
name varchar(16))insert into @tb
select 'CP','12P'
UNION ALL
select 'CP','20P'
UNION ALL
select 'CP','20P'
UNION ALL
select 'CP','2P'
UNION ALL
select 'CP','6P'
UNION ALL
select 'FP-NEW','12P'
UNION ALL
select 'QT','12P'SELECT * FROM @TB ORDER BY CODE ASC,CONVERT(INT,LEFT(NAME,LEN(NAME)-1)) ASC
---------------------------
code name
------------
CP 2P
CP 6P
CP 12P
CP 20P
CP 20P
FP-NEW 12P
QT 12P
declare @t table(col1 varchar(20),col2 varchar(20))insert @t select 'cp','12p'
union all select 'cp','20p'
union all select 'cp','20p'
union all select 'cp','2p'
union all select 'cp','6p'
union all select 'fp','new 12p'
union all select 'qt','12p'select
*
from @t
order by col1,left(col2,patindex('%[^0-9]%',col2))
--这个100%对
declare @t table(col1 varchar(20),col2 varchar(20)) insert @t select 'cp','12p'
union all select 'cp','20p'
union all select 'cp','20p'
union all select 'cp','2p'
union all select 'cp','6p'
union all select 'fp','new 12p'
union all select 'qt','12p' select
*
from @t
order by col1,convert(int,left(col2,patindex('%[^0-9]%',col2)-1))
union all select 'cp','20p'
union all select 'cp','20p'
union all select 'cp','2p'
union all select 'cp','6p'
union all select 'fp-new','12p'
union all select 'qt','12p'select * from @t
order by col1,left(col2,patindex('%[^0-9]%',col2)-1)+0
----------
cp 2p
cp 6p
cp 12p
cp 20p
cp 20p
fp-new 12p
qt 12p
因为我提供的那些数据是一个产品表里的一个字段PrdNm的值,这个表还有很多字段.
我有一个复杂的select语句,会得到很多字段,希望最后结果是按PrdNm的上述排序顺序显示出来.