sql - 该行必须根据列值在结果集中以相同的顺序显示

Question

我正在使用 SQL Server 2008 R2。我有以下数据:

ID   Value   OrderNumber
1      A         NULL
2      E          4
3      C         NULL
4      B         NULL
5      F          2
6      D         NULL

我想编写一个查询，该查询必须根据 OrderNumber 值获取按 OrderNumber 列排序的数据。查询结果必须如下:

ID      Value     OrderNumber
1         A           NULL
5         F            2  --indicates row must be second in result set. 
3         C           NULL
2         E            4  --indicates row must be fourth in result set.
4         B           NULL
6         D           NULL

感谢阅读和回答。

Answer 1

我已经尝试了多种不同的方法，但我能找到的能够以有保证的方式产生所需结果的唯一方法是:

declare @t table (ID int not null,Value char(1) not null,OrderNumber int null)
insert into @T(ID,Value,OrderNumber) values
(1,'A',NULL),
(2,'E',4),
(3,'C',NULL),
(4,'B',NULL),
(5,'F',2),
(6,'D',NULL)

;With Nbrs as (
    select ROW_NUMBER() OVER (ORDER BY ID) as n from @t
), AvailableNbrs as (
    select n,ROW_NUMBER() OVER (ORDER BY n) as rn from Nbrs where n not in (select OrderNumber from @t where OrderNumber is not null)
), RequiredOrders as (
    select ID,ROW_NUMBER() OVER (ORDER BY ID) as rn from @t where OrderNumber is null
)
select
    *,COALESCE(OrderNumber,an.n) as FinalOrder
from
    @t t
        left join
    RequiredOrders ro
        on
            t.ID = ro.ID
        left join
    AvailableNbrs an
        on
            ro.rn = an.rn
order by COALESCE(OrderNumber,an.n)

我们在哪里使用一些 CTE 来查找 OrderNumber当前未分配的 s，并将这些 1-1 与没有 OrderNumber 的行匹配.

结果:

|--------- @t --------------| |----- RequiredOrders ---------| |----- AvailableNbrs -------------------| |- COALESCE -------|
ID          Value OrderNumber ID          rn                   n                    rn                   FinalOrder
----------- ----- ----------- ----------- -------------------- -------------------- -------------------- --------------------
1           A     NULL        1           1                    1                    1                    1
5           F     2           NULL        NULL                 NULL                 NULL                 2
3           C     NULL        3           2                    3                    2                    3
2           E     4           NULL        NULL                 NULL                 NULL                 4
4           B     NULL        4           3                    5                    3                    5
6           D     NULL        6           4                    6                    4                    6