order by substring(字段名,2,len(字段名))
解决方案 »
- 如何提高我的SQL语句的速度
- sqlserver2005: 固定的几个表,如何灵活地来回切换测试数据?
- 就是两张表,连接查的时候不知道怎么处理...
- 怎么将SQLSERVER2000中的数据库设置为“GBK”
- sql server 2005与simulink
- datetime类型的比较问题
- 紧急求助:关于两个数据库的相同表的表结构(字段)如何比较?
- 急!!!請問如何通過DTS去刪除和插入數據到Access數據庫呢???
- 请教关于ESQL\C入门问题?
- subString用法
- 按多字段排序
- sql server 长时间update后 update出现time out--急找邹建急找邹建急找邹建大哥!!!!!!!!在线急等!!!不睡觉不吃饭等!
order by convert(int,substring(字段名,2,len(字段名)))
create table #T(ID varchar(10))declare @s varchar(1000)
select @s='insert into #t select '+replace(''''+'A1,A11,A12,A2,A3,A4,A5,A6,A7,A8,A9,A10'+'''',',',''' union all select ''')
exec(@s)select * from #t order by len(id),id
ID
----------
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12(12 行受影响)
(name varchar(10))
insert into #
select 'A1' UNION ALL
select 'A2' UNION ALL
select 'A3' UNION ALL
select 'A4' UNION ALL
select 'A5' UNION ALL
select 'A6' UNION ALL
select 'A7' UNION ALL
select 'A8' UNION ALL
select 'A9' UNION ALL
select 'A10' UNION ALL
select 'A11' UNION ALL
select 'A12'
--原来排序
SELECT * FROM # ORDER BY name /*
name
----------
A1
A10
A11
A12
A2
A3
A4
A5
A6
A7
A8
A9(所影响的行数为 12 行)*/
SELECT * FROM # ORDER BY cast(substring(NAME,2,len(name)-1)as int)--现在排序/*
name
----------
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12(所影响的行数为 12 行)*/
order by len(字段名),字段名这个方法不错啊!