22,300
社区成员




USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
SortId INT,
bSortId INT
)
SET NOCOUNT ON
INSERT INTO t VALUES (12,NULL)
INSERT INTO t VALUES (4,NULL)
INSERT INTO t VALUES (9,NULL)
INSERT INTO t VALUES (2,NULL)
INSERT INTO t VALUES (10,NULL)
INSERT INTO t VALUES (1,1)
INSERT INTO t VALUES (3,2)
INSERT INTO t VALUES (5,3)
INSERT INTO t VALUES (6,4)
INSERT INTO t VALUES (6,5)
INSERT INTO t VALUES (7,6)
INSERT INTO t VALUES (8,7)
INSERT INTO t VALUES (11,8)
INSERT INTO t VALUES (13,9)
INSERT INTO t VALUES (14,10)
INSERT INTO t VALUES (13,11)
INSERT INTO t VALUES (14,12)
select sortid,bsortid
from
(select
sortid
,bsortid
,astb=isnull(bsortid,(select max(b.bsortid) from t b where b.SortId <a.SortId))
from t a)c
order by astb,sortid
/*
1 1
2 NULL
3 2
4 NULL
5 3
6 4
6 5
7 6
8 7
9 NULL
10 NULL
11 8
12 NULL
13 9
14 10
13 11
14 12
*/
IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL
DROP TABLE #tmp_data
GO
CREATE TABLE #tmp_data(
SortId INT,
bSortId INT
)
INSERT INTO #tmp_data VALUES (12,NULL)
INSERT INTO #tmp_data VALUES (4,NULL)
INSERT INTO #tmp_data VALUES (9,NULL)
INSERT INTO #tmp_data VALUES (2,NULL)
INSERT INTO #tmp_data VALUES (10,NULL)
INSERT INTO #tmp_data VALUES (1,1)
INSERT INTO #tmp_data VALUES (3,2)
INSERT INTO #tmp_data VALUES (5,3)
INSERT INTO #tmp_data VALUES (6,4)
INSERT INTO #tmp_data VALUES (6,5)
INSERT INTO #tmp_data VALUES (7,6)
INSERT INTO #tmp_data VALUES (8,7)
INSERT INTO #tmp_data VALUES (11,8)
INSERT INTO #tmp_data VALUES (13,9)
INSERT INTO #tmp_data VALUES (14,10)
INSERT INTO #tmp_data VALUES (13,11)
INSERT INTO #tmp_data VALUES (14,12)
Select SortID,bSortID
From (Select SortID,Row_No=Row_number() over (Order By SortID) From #tmp_data) a
Join (Select bSortID,Row_No=Row_number() over (Order By bSortID) From #tmp_data) b on a.Row_No=b.Row_No
SortID bSortID
----------- -----------
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 1
6 2
7 3
8 4
9 5
10 6
11 7
12 8
13 9
13 10
14 11
14 12
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
SortId INT,
bSortId INT
)
SET NOCOUNT ON
INSERT INTO t VALUES (12,NULL)
INSERT INTO t VALUES (4,NULL)
INSERT INTO t VALUES (9,NULL)
INSERT INTO t VALUES (2,NULL)
INSERT INTO t VALUES (10,NULL)
INSERT INTO t VALUES (1,1)
INSERT INTO t VALUES (3,2)
INSERT INTO t VALUES (5,3)
INSERT INTO t VALUES (6,4)
INSERT INTO t VALUES (6,5)
INSERT INTO t VALUES (7,6)
INSERT INTO t VALUES (8,7)
INSERT INTO t VALUES (11,8)
INSERT INTO t VALUES (13,9)
INSERT INTO t VALUES (14,10)
INSERT INTO t VALUES (13,11)
INSERT INTO t VALUES (14,12)
SELECT * FROM t
ORDER BY
bSortId,SortId
/*
SortId bSortId
----------- -----------
2 NULL
4 NULL
9 NULL
10 NULL
12 NULL
1 1
3 2
5 3
6 4
6 5
7 6
8 7
11 8
13 9
14 10
13 11
14 12
*/