22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM
(select SUBSTRING(someName,3,1)+case when ISNUMERIC(SUBSTRING(someName,4,1))=1 then SUBSTRING(someName,4,1) else '' end as Col,someName from [table]) AS T
order by SUBSTRING(someName,1,2),convert(int,Col) ASC
--表达式在order by 不能用别名转换,只能用表达式
ORDER BY convert(int,SUBSTRING(someName,3,1)+case when ISNUMERIC(SUBSTRING(someName,4,1))=1 then SUBSTRING(someName,4,1) else '' END)
select * FROM(
COL=(SUBSTRING(someName,3,1)+case when ISNUMERIC(SUBSTRING(someName,4,1))=1 then SUBSTRING(someName,4,1) else '' end)
from table)T1
order by SUBSTRING(someName,1,2), convert(int,Col) ASC
用了别名 但是,你用convert 函数的时候还没走到那一步,所以 col是不存在的。 会报错 SELECT CONVERT(INT,(SUBSTRING(someName, 3, 1)
+ CASE WHEN ISNUMERIC(SUBSTRING(someName, 4, 1)) = 1
THEN SUBSTRING(someName, 4, 1)
ELSE ''
END)) AS Col
FROM table
ORDER BY SUBSTRING(someName, 1, 2) ,
Col ASC