34,590
社区成员
发帖
与我相关
我的任务
分享1.1.1,1.1.2此类序号排序问题
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前面去，怎么处理？
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前面去，怎么处理？
...全文
请发表友善的回复…
发表回复
nianran520 2010-03-23
- 打赏
- 举报
--参考
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)
yhtapmys 2010-03-23
- 打赏
- 举报
[Quote=引用 9 楼 ldslove 的回复:]
SQL code
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('8……
[/Quote]
顶东升哥
SQL code
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('8……
[/Quote]
顶东升哥
东那个升 2010-03-23
- 打赏
- 举报
靠，怎么发了2个。
东那个升 2010-03-23
- 打赏
- 举报
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_id
with 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 path
part_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 行受影响)东那个升 2010-03-23
- 打赏
- 举报
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_id
with 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 path
part_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 行受影响)
aaron_gl 2010-03-23
- 打赏
- 举报
select * from tb order by cast(left(no_id,3) as real)*10
claro 2010-03-23
- 打赏
- 举报
不过在order by中用函数处理较影响性能，建议看4F。
claro 2010-03-23
- 打赏
- 举报
关于数据排序案例
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
*/
--小F-- 2010-03-23
- 打赏
- 举报
[Quote=引用 4 楼 dawugui 的回复:]
SQL code
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('8……
[/Quote]
小哥 你太帅鸟..
SQL code
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('8……
[/Quote]
小哥 你太帅鸟..
dawugui 2010-03-23
- 打赏
- 举报
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 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 , px3
drop 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 行）
*/
阿aaS 2010-03-23
- 打赏
- 举报
老黎 2010-03-23
- 打赏
- 举报
up
求拆分方法
--小F-- 2010-03-23
- 打赏
- 举报
按.拆分 转化成int的再排
