create table tb(id varchar(10)) insert into tb select '222.1' union all select '222.1.1' union all select '212.1.1' union all select '212.1.2' union all select '212.1.2.1' select * from tb order by left(id,3) desc,id/* id ---------- 222.1 222.1.1 212.1.1 212.1.2 212.1.2.1
select * from tb order by len(col)-len(replace(col,'.',''))
select * from tb order by len(col)-len(replace(col,'.','')) desc
我是用SQL2000,怎么提示replace函数未定义呢?
select A.*From ( select '222.1' as column_s union all select '222.1.1' union all select '212.1.1' union all select '212.1.2' union all select '212.1.2.1' ) A order by left(A.column_s,3) desc ,A.column_s asc =================== column_s --------- 222.1 222.1.1 212.1.1 212.1.2 212.1.2.1(5 行受影响)
insert into tb
select '222.1' union all
select '222.1.1' union all
select '212.1.1' union all
select '212.1.2' union all
select '212.1.2.1' select * from tb order by left(id,3) desc,id/*
id
----------
222.1
222.1.1
212.1.1
212.1.2
212.1.2.1
select * from tb order by len(col)-len(replace(col,'.','')) desc
select A.*From (
select '222.1' as column_s union all
select '222.1.1' union all
select '212.1.1' union all
select '212.1.2' union all
select '212.1.2.1' ) A
order by left(A.column_s,3) desc ,A.column_s asc
===================
column_s
---------
222.1
222.1.1
212.1.1
212.1.2
212.1.2.1(5 行受影响)