select * from youtab order by case substring(col,3,1) when '0' then (substring(col,1,2) + '20' + substring(col,3,len(col)-2)) else (substring(col,1,2) + '19' + substring(col,3,len(col)-2)) end
select * from t1 order by substring(dd,1,2) + case substring(dd,3,2) when '99' then '1999' else '20' + substring(dd,3,2) end + substring(dd,5,len(dd)-5)
if exists (select * from sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp1] gocreate table temp1(bh int not null,mess varchar(10) null) goinsert into temp1 select 1,'1-01A001' union all select 2,'1-99A002' union all select 3,'1-00A001' goselect * from temp1 /* bh mess 1 1-01A001 2 1-99A002 3 1-00A001 */SET DATEFORMAT ydm go select * from temp1 order by substring(mess,1,2)+convert(varchar(10),year(cast(substring(mess,3,2)+'/01/01' as datetime)))+ right(mess,len(mess)-4) /* bh mess 2 1-99A002 3 1-00A001 1 1-01A001 */
按1951-2050排序: select * from t order by case when substring(col,3,4)>'50' then stuff(col,3,0,'19') else stuff(col,3,0,'20') end
order by case substring(col,3,1)
when '0' then (substring(col,1,2) + '20' + substring(col,3,len(col)-2))
else (substring(col,1,2) + '19' + substring(col,3,len(col)-2))
end
order by substring(dd,1,2) +
case substring(dd,3,2) when '99' then '1999'
else '20' + substring(dd,3,2) end
+ substring(dd,5,len(dd)-5)
drop table [dbo].[temp1]
gocreate table temp1(bh int not null,mess varchar(10) null)
goinsert into temp1
select 1,'1-01A001' union all
select 2,'1-99A002' union all
select 3,'1-00A001' goselect * from temp1
/*
bh mess
1 1-01A001
2 1-99A002
3 1-00A001
*/SET DATEFORMAT ydm
go
select * from temp1 order by substring(mess,1,2)+convert(varchar(10),year(cast(substring(mess,3,2)+'/01/01' as datetime)))+ right(mess,len(mess)-4)
/*
bh mess
2 1-99A002
3 1-00A001
1 1-01A001
*/
select * from t order by case when substring(col,3,4)>'50' then stuff(col,3,0,'19') else stuff(col,3,0,'20') end