项目实战之sql的order by用法

表名：projectinfo
字段名
[b]project_id [/b] [b]project_name[/b]
0660-10430001 “中铁.江佑铂庭”一期房地产开发项目01—06
0660-10490002 南京质量技术检测中心变电所高压开关柜
0660-10490002/1 南京质量技术检测中心变电所高压开关柜
0660-10490003 南京质量技术检测中心变电所低压开关柜
0660-10490003/1 南京质量技术检测中心变电所高压开关柜
0660-10490004 南京质量技术检测中心变电所变压器
0660-10500005 江苏省委党校新校区所需电缆
0660-10500005/1 江苏省委党校新校区
0660-104010030006 磨齿机采购
0660-104010030006/1 磨齿机采购
0660-10400007 直读光谱仪
0660-10400007/1 直读光谱仪

最好的取右四位进行排序方法如下：
select p1.project_id
from projectinfo p1
order by right(case when charindex('/',p1.project_id)>0 then substring(p1.project_id,0,charindex('/',p1.project_id))else p1.project_id end,4) asc;
或
select project_id from projectinfo
order by
cast
(
case
when charindex('/',project_id)>0
then substring(project_id,charindex('/',project_id)-4,4)
else right(project_id,4)
end
as int
)
asc;
也可以不转换为int

另外其他不足的排序如下：
select p.project_id
from projectinfo p
where charindex('/',p.project_id)>0
order by right(substring(p.project_id,0,charindex('/',p.project_id)),4) ;