--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([COL] varchar(10)) insert [TB] select 'YC-DB-3-01' union all select 'YC-DB-5' union all select 'YC-DB-6' union all select 'ac-3' union all select 'YC-DB-3-02' union all select 'YC-DB-7' union all select 'YC-DB-3-03' union all select 'YC-DB-4' union all select 'ac-1' union all select 'ac-2' union all select 'YC-DB-2' union all select 'a1' union all select 'a2' union all select 'YC-DB-7-01' union all select 'YC-DB-7-02' union all select 'a4' union all select 'a5' union all select 'a3' union all select 'ac-04' union all select 'ac-05'select * from [TB]SELECT col FROM TB ORDER BY len(col),col /* a1 a2 a3 a4 a5 ac-1 ac-2 ac-3 ac-04 ac-05 YC-DB-2 YC-DB-4 YC-DB-5 YC-DB-6 YC-DB-7 YC-DB-3-01 YC-DB-3-02 YC-DB-3-03 YC-DB-7-01 YC-DB-7-02*/
ac-05
为什么突然变成04 05
而不是 4 5呢
数据有点乱
select *
from tb
order by replace(编号,'-0','-'),len(replace(编号,'-0','-'))
乱了,结果如下a1
a2
a3
a4
a5
ac-1
ac-2
ac-3
ac-04
ac-05
YC-DB-2
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
YC-DB-7-01
YC-DB-7-02
if object_id('[TB]') is not null drop table [TB]
create table [TB]([COL] varchar(10))
insert [TB]
select 'YC-DB-3-01' union all
select 'YC-DB-5' union all
select 'YC-DB-6' union all
select 'ac-3' union all
select 'YC-DB-3-02' union all
select 'YC-DB-7' union all
select 'YC-DB-3-03' union all
select 'YC-DB-4' union all
select 'ac-1' union all
select 'ac-2' union all
select 'YC-DB-2' union all
select 'a1' union all
select 'a2' union all
select 'YC-DB-7-01' union all
select 'YC-DB-7-02' union all
select 'a4' union all
select 'a5' union all
select 'a3' union all
select 'ac-04' union all
select 'ac-05'select * from [TB]SELECT col FROM TB ORDER BY len(col),col
/*
a1
a2
a3
a4
a5
ac-1
ac-2
ac-3
ac-04
ac-05
YC-DB-2
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-7-01
YC-DB-7-02*/
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
正确如下
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-2
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
为什么YC-DB-2 是在3的后面 而不是前面
SELECT *
FROM TB
ORDER BY len(col),left(col,7),colCOL
--------------------
a1
a2
a3
a4
a5
ac-1
ac-2
ac-3
ac-04
ac-05
YC-DB-2
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-7-01
YC-DB-7-02(20 row(s) affected)
不就可以了。
SELECT *
FROM TB
ORDER BY left(col,7),len(col),colCOL
--------------------
a1
a2
a3
a4
a5
ac-04
ac-05
ac-1
ac-2
ac-3
YC-DB-2
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
YC-DB-7-01
YC-DB-7-02(20 row(s) affected)
select * from tb
order by len(编号), (
case when charindex('-',编号,1) >0
then (case when len(编号)-len(replace(编号,'-',''))>2 then ( substring(reverse(编号),charindex('-',reverse(编号),1)+1,1) ) else reverse(substring(reverse(编号),1,charindex('-',reverse(编号),1)-1)) end)
else (case when isnumeric(right(编号,2))=1 then right(编号,2)
else right(编号,1) end) end)
结果是
a1
a2
a3
a4
a5
ac-1
ac-2
ac-3
ac-04
ac-05
YC-DB-2
YC-DB-4
YC-DB-5
YC-DB-6
YC-DB-7
YC-DB-3-01
YC-DB-3-02
YC-DB-3-03
YC-DB-7-01
YC-DB-7-02一模一样的语句在他那执行就是不行。原因暂时还没有找出来。
FROM TB
ORDER BY len(col),left(col,7),colselect * from [TB]SELECT col FROM TB ORDER BY len(col),col18楼和7楼接近标准答案。。具体用哪种方法。我再测试。
哪个帮我解释一下这两条语句?
然后再查询
ORDER BY len(col),left(col,7),col ---先按照col字段长度排序,在此基础上再根据前7码进行内排序。