1.1.1,1.1.2此类序号排序问题

Question

part_id parent_id qty order_id lev no_id
80 123456 5 1 1 1
8001 80 5 1 2 1.1
800101 8001 5 1 3 1.1.1
800102 8001 5 2 3 1.1.2
8002 80 5 2 2 1.2
800201 8002 5 1 3 1.2.1
800202 8002 5 2 3 1.2.2
10 123456 5 10 1 10
81 123456 5 2 1 2
按no_id排序SQL,no_id为10的跑到2前面去了，烦是有1带头的都会跑到2前面去，怎么处理？

Answer 1

create table tb(part_id varchar(10),parent_id varchar(10),qty int,order_id int,lev int,no_id varchar(10))
insert into tb values('80'    , '123456'  ,5 ,1  ,1 ,'1')
insert into tb values('8001'  , '80'      ,5 ,1  ,2 ,'1.1')
insert into tb values('800101', '8001'    ,5 ,1  ,3 ,'1.1.1')
insert into tb values('800102', '8001'    ,5 ,2  ,3 ,'1.1.2')
insert into tb values('8002'  , '80'      ,5 ,2  ,2 ,'1.2')
insert into tb values('800201', '8002'    ,5 ,1  ,3 ,'1.2.1')
insert into tb values('800202', '8002'    ,5 ,2  ,3 ,'1.2.2')
insert into tb values('10'    , '123456'  ,5 ,10 ,1 ,'10')
insert into tb values('81'    , '123456'  ,5 ,2  ,1 ,'2')goselect part_id ,parent_id ,qty ,order_id ,lev ,no_id from
(
select * , px1 = cast(no_id as int) , px2 = 0 , px3 = 0 from tb where charindex('.',no_id) = 0
union all
select * , px1 = left(no_id , charindex('.',no_id) -1), px2 = substring(no_id , charindex('.',no_id)+1,len(no_id)) , px3 = 0 from tb where len(no_id) - len(replace(no_id,'.','')) = 1
union all
select * , px1 = left(no_id , charindex('.',no_id) -1), 
           px2 = substring(no_id , charindex('.',no_id)+1, charindex('.',no_id ,charindex('.',no_id)+1) - charindex('.',no_id) - 1) , 
           px3 = substring(no_id , charindex('.',no_id ,charindex('.',no_id)+1) + 1, len(no_id))
from tb where len(no_id) - len(replace(no_id,'.','')) = 2
) t
order by px1 , px2 , px3drop table tb/*
part_id    parent_id  qty         order_id    lev         no_id      
---------- ---------- ----------- ----------- ----------- ---------- 
80         123456     5           1           1           1
8001       80         5           1           2           1.1
800101     8001       5           1           3           1.1.1
800102     8001       5           2           3           1.1.2
8002       80         5           2           2           1.2
800201     8002       5           1           3           1.2.1
800202     8002       5           2           3           1.2.2
81         123456     5           2           1           2
10         123456     5           10          1           10（所影响的行数为 9 行）
*/

Answer 2

关于数据排序案例select *
from (select '1' no_id union all
select '1.1' union all
select '1.1.1' union all
select '1.1.2' union all
select '1.2' union all
select '1.2.1' union all
select '1.2.2' union all
select '10' union all
select '2') a 
order by cast(REPLACE(no_id,'.','') as int)  
/*
1
2
10
1.1
1.2
1.1.1
1.1.2
1.2.1
1.2.2
*/select *
from (select '1' no_id union all
select '1.1' union all
select '1.1.1' union all
select '1.1.2' union all
select '1.2' union all
select '1.2.1' union all
select '1.2.2' union all
select '10' union all
select '2') a 
order by cast(left(no_id,3) as float)  
/*
1
1.1.1
1.1.2
1.1
1.2
1.2.1
1.2.2
2
10
*/

Answer 3

不过在order by中用函数处理较影响性能，建议看4F。

Answer 4

select * from tb order by  cast(left(no_id,3) as real)*10

Answer 5

create table tb(part_id varchar(10),parent_id varchar(10),qty int,order_id int,lev int,no_id varchar(10))
insert into tb values('80'    , '123456'  ,5 ,1  ,1 ,'1')
insert into tb values('8001'  , '80'      ,5 ,1  ,2 ,'1.1')
insert into tb values('800101', '8001'    ,5 ,1  ,3 ,'1.1.1')
insert into tb values('800102', '8001'    ,5 ,2  ,3 ,'1.1.2')
insert into tb values('8002'  , '80'      ,5 ,2  ,2 ,'1.2')
insert into tb values('800201', '8002'    ,5 ,1  ,3 ,'1.2.1')
insert into tb values('800202', '8002'    ,5 ,2  ,3 ,'1.2.2')
insert into tb values('10'    , '123456'  ,5 ,10 ,1 ,'10')
insert into tb values('81'    , '123456'  ,5 ,2  ,1 ,'2')go
select * from tb order by part_idwith cte as
(select *, cast(row_number() over(order by order_id ) as varbinary(max)) as path 
from tb where lev=1 
union all
select a.*,b.path+cast(row_number() over(partition by a.parent_id order by a.order_id) as binary(4)) 
from tb a,cte b where a.parent_id=b.part_id
)
select * from cte order by pathpart_id    parent_id          qty    order_id         lev no_id      path
---------- ---------- ----------- ----------- ----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
80         123456               5           1           1 1          0x0000000000000001
8001       80                   5           1           2 1.1        0x000000000000000100000001
800101     8001                 5           1           3 1.1.1      0x00000000000000010000000100000001
800102     8001                 5           2           3 1.1.2      0x00000000000000010000000100000002
8002       80                   5           2           2 1.2        0x000000000000000100000002
800201     8002                 5           1           3 1.2.1      0x00000000000000010000000200000001
800202     8002                 5           2           3 1.2.2      0x00000000000000010000000200000002
81         123456               5           2           1 2          0x0000000000000002
10         123456               5          10           1 10         0x0000000000000003(9 行受影响)

Answer 6

create table tb(part_id varchar(10),parent_id varchar(10),qty int,order_id int,lev int,no_id varchar(10))
insert into tb values('80'    , '123456'  ,5 ,1  ,1 ,'1')
insert into tb values('8001'  , '80'      ,5 ,1  ,2 ,'1.1')
insert into tb values('800101', '8001'    ,5 ,1  ,3 ,'1.1.1')
insert into tb values('800102', '8001'    ,5 ,2  ,3 ,'1.1.2')
insert into tb values('8002'  , '80'      ,5 ,2  ,2 ,'1.2')
insert into tb values('800201', '8002'    ,5 ,1  ,3 ,'1.2.1')
insert into tb values('800202', '8002'    ,5 ,2  ,3 ,'1.2.2')
insert into tb values('10'    , '123456'  ,5 ,10 ,1 ,'10')
insert into tb values('81'    , '123456'  ,5 ,2  ,1 ,'2')go
select * from tb order by part_idwith cte as
(select *, cast(row_number() over(order by order_id ) as varbinary(max)) as path 
from tb where lev=1 
union all
select a.*,b.path+cast(row_number() over(partition by a.parent_id order by a.order_id) as binary(4)) 
from tb a,cte b where a.parent_id=b.part_id
)
select * from cte order by pathpart_id    parent_id          qty    order_id         lev no_id      path
---------- ---------- ----------- ----------- ----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
80         123456               5           1           1 1          0x0000000000000001
8001       80                   5           1           2 1.1        0x000000000000000100000001
800101     8001                 5           1           3 1.1.1      0x00000000000000010000000100000001
800102     8001                 5           2           3 1.1.2      0x00000000000000010000000100000002
8002       80                   5           2           2 1.2        0x000000000000000100000002
800201     8002                 5           1           3 1.2.1      0x00000000000000010000000200000001
800202     8002                 5           2           3 1.2.2      0x00000000000000010000000200000002
81         123456               5           2           1 2          0x0000000000000002
10         123456               5          10           1 10         0x0000000000000003(9 行受影响)

Answer 7

--参考
drop table test
go
create table test 
( 
id  nvarchar(10), 
name nvarchar(20), 
code  nvarchar(100) ) 
insert into test select '01','a1' ,'1817A0Y003.1' 
insert into test select '02','a2' ,'1817A0Y003.2.1' 
insert into test select '03','a9' ,'1817A0Y003.2.2' 
insert into test select '07','a15' ,'1817A0Y003.3' 
insert into test select '05','a18' ,'1817A0Y003.4.1.2.1' 
insert into test select '04','a8' ,'1817A0Y003.4.1.2.2' 
insert into test select '010','a55' ,'1817A0Y003.4.1' 
insert into test select '012','a25' ,'1817A0Y003.4.3.1' 
insert into test select '017','a14' ,'1817A0Y003.4.5' 
insert into test select '027','a24' ,'1817A0Y003.4.4' 
insert into test select '0107','a104' ,'1817A0Y003.14.5' 
insert into test select '0207','a204' ,'1817A0Y003.14.4' 
--函数
create function dbo.getcode(@code varchar(8000))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=isnull(@str+'.','')+case number when 1 then code else right('0000'+code,4) end
from 
(select number,code=substring(@code,number,charindex('.',@code+'.',number)- number)
from master..spt_values
where type='p' 
and substring('.'+@code,number,1)='.') t
return @str
end
select * from test 
order by dbo.getcode(code)