有个表,有这样两个字段a varchar,b int
数据如下:
a b
10 null
10.1 1
10.1.1 null
10.1.2 null
10.1.3 null
10.1.4 null
10.2 0
10.3 2
10.4 3
10.5 4现在要实现排序的结果
a b
10 null
10.2 0
10.1 1
10.1.1 null
10.1.2 null
10.1.3 null
10.1.4 null
10.3 2
10.4 3
10.5 4
如何实现,在线等
数据如下:
a b
10 null
10.1 1
10.1.1 null
10.1.2 null
10.1.3 null
10.1.4 null
10.2 0
10.3 2
10.4 3
10.5 4现在要实现排序的结果
a b
10 null
10.2 0
10.1 1
10.1.1 null
10.1.2 null
10.1.3 null
10.1.4 null
10.3 2
10.4 3
10.5 4
如何实现,在线等
--sql2005:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(6),[b] int)
insert [tb]
select '10',null union all
select '10.1',1 union all
select '10.1.1',null union all
select '10.1.2',null union all
select '10.1.3',null union all
select '10.1.4',null union all
select '10.2',0 union all
select '10.3',2 union all
select '10.4',3 union all
select '10.5',4
go
--select * from [tb];with t1 as
(select a,b,lvl=len(a)-len(replace(a,'.','')) from tb)
,t2 as
(
select a,b,p=cast(isnull(b,9999) as varchar(8000)),lvl from t1 where lvl=0
union all
select a.a,a.b,b.p+rtrim(isnull(a.b,9999)),a.lvl from t1 a join t2 b
on a.a like b.a+'.%' and a.lvl=b.lvl+1
)
select a,b from t2 order by p,a
/*
a b
------ -----------
10 NULL
10.2 0
10.1 1
10.1.1 NULL
10.1.2 NULL
10.1.3 NULL
10.1.4 NULL
10.3 2
10.4 3
10.5 4(10 行受影响)
*/
DECLARE @T TABLE(
A VARCHAR(10),
B INT
)
INSERT INTO @T
SELECT '10' ,null UNION ALL
SELECT '10.1' ,1 UNION ALL
SELECT '10.1.1' ,null UNION ALL
SELECT '10.1.2' ,null UNION ALL
SELECT '10.1.3' ,null UNION ALL
SELECT '10.1.4' ,null UNION ALL
SELECT '10.2' ,0 UNION ALL
SELECT '10.3' ,2 UNION ALL
SELECT '10.4' ,3 UNION ALL
SELECT '10.7' ,5 UNION ALL
SELECT '10.6' ,NULL UNION ALL
SELECT '10.5' ,4
CREATE TABLE TEMP (
ID INT,
A VARCHAR(10),
B INT
)
INSERT INTO TEMP
SELECT ROW_NUMBER() OVER (
ORDER BY
CASE WHEN CONVERT(VARCHAR(10),B) IS NULL THEN A ELSE CONVERT(VARCHAR(10),B) END
),A,B FROM @T
ORDER BY
CASE WHEN CONVERT(VARCHAR(10),B) IS NULL THEN A ELSE CONVERT(VARCHAR(10),B) END
--SELECT * FROM TEMPUPDATE TEMP SET ID=
CASE WHEN EXISTS(
SELECT DISTINCT 1 FROM TEMP t2 WHERE T2.A>TEMP.A
AND (LEN(T2.A)-LEN(REPLACE(T2.A,'.','')))>(LEN(TEMP.A)-LEN(REPLACE(TEMP.A,'.','')))
) THEN (SELECT MIN(T3.ID)-1 FROM TEMP t3 WHERE T3.A>TEMP.A )
WHEN EXISTS(
SELECT DISTINCT 1 FROM TEMP t2 WHERE T2.A<TEMP.A
AND (LEN(T2.A)-LEN(REPLACE(T2.A,'.','')))>(LEN(TEMP.A)-LEN(REPLACE(TEMP.A,'.','')))
) THEN (SELECT MAX(T3.ID)+1 FROM TEMP t3 WHERE T3.A<TEMP.A )
ELSE
ID
END
WHERE B IS NULL
SELECT A,B FROM TEMP ORDER BY ID,B
DROP TABLE TEMP--吃完饭回来竟然还没人贴答案,那就我来
/*
10 NULL
10.2 0
10.1 1
10.1.1 NULL
10.1.2 NULL
10.1.3 NULL
10.1.4 NULL
10.3 2
10.4 3
10.5 4
10.6 NULL
10.7 5
*/