Declare @T Table(A Varchar(10))Insert Into @T Select '1' Union Select '4' Union Select '10'Select * From @T Order By Right('000000'+A,2) --这儿的“0”的个数和2可以根据你的字段内容的实际长度进行调整,原理就是在左边加足够的0!保证以01,04,10...的方式来排序。
转成int是不成功的。 楼上的正确。 看看还有别的办法没有。
转成int的可以啊.--借用lzhs(快乐至上)的数据 Declare @T Table(A Varchar(10)) Insert Into @T Select '1' Union Select '4' Union Select '10'Select * From @T order by cast(a as int)
谢谢,正如楼上所说,改列还有字母。而我要排序的记录是经过where出来的,全部是数字。 Declare @T Table(A Varchar(10),B Varchar(10)) Insert Into @T Select '1',"a" Union Select '4',"a" Union Select '10',"a" union select "dd","b"Select * From @T where B='a' order by cast(a as int)
sorry,是我搞错了。上面的答案都是对的。
Declare @T Table(A Varchar(10))Insert Into @T Select '1' Union Select '4' Union Select '10' Union SELECT 'a'SELECT * FROM @t WHERE ISNUMERIC(A)=1 ORDER BY CAST(A AS INT) --如果非数字的值要转为特定数字可以是:假设这里非数字的统一为100SELECT * FROM @t ORDER BY CAST(ISNULL(STUFF(RTRIM(NULLIF(ISNUMERIC(A),0))+A,1,1,''),100) AS INT)--当然,也可以使用CASE WHEN
Select '1' Union
Select '4' Union
Select '10'Select * From @T
Order By Right('000000'+A,2) --这儿的“0”的个数和2可以根据你的字段内容的实际长度进行调整,原理就是在左边加足够的0!保证以01,04,10...的方式来排序。
楼上的正确。
看看还有别的办法没有。
Declare @T Table(A Varchar(10))
Insert Into @T
Select '1' Union
Select '4' Union
Select '10'Select * From @T order by cast(a as int)
Declare @T Table(A Varchar(10),B Varchar(10))
Insert Into @T
Select '1',"a" Union
Select '4',"a" Union
Select '10',"a" union
select "dd","b"Select * From @T where B='a' order by cast(a as int)
Select '1' Union
Select '4' Union
Select '10' Union
SELECT 'a'SELECT * FROM @t WHERE ISNUMERIC(A)=1 ORDER BY CAST(A AS INT)
--如果非数字的值要转为特定数字可以是:假设这里非数字的统一为100SELECT * FROM @t ORDER BY CAST(ISNULL(STUFF(RTRIM(NULLIF(ISNUMERIC(A),0))+A,1,1,''),100) AS INT)--当然,也可以使用CASE WHEN