SQL:固定行和行号计算

Question

我们需要使用以下规则为所有行分配行号

• 固定的行应具有相同的行号
• 否则按GMD排序

例子:

 ID  GMD   IsPinned 

 1   2.5    0        
 2    0     1        
 3    2     0        
 4    4     1        
 5    3     0       

应该输出

ID  GMD   IsPinned  RowNo

5    3    0          1   
2    0    1          2  
1    2.5  0          3  
4    4    1          4  
3    2    0          5  

请注意 Id 的 2 和 4 的行号保持不变，因为它们分别固定为值 2 和 4，即使 GMD 没有任何顺序其余行Id的1、3、5行号使用GMD desc排序

我尝试使用 RowNumber SQL 2012，但是它会将固定的项目从它们的位置推出

Answer 1

这里有一个基于集合的方法来解决这个问题。注意第一个 CTE如果您已经有 Numbers 则不需要数据库中的表:

declare @t table (ID int,GMD decimal(5,2),IsPinned bit)
insert into @t (ID,GMD,IsPinned) values
(1,2.5,0), (2, 0 ,1), (3, 2 ,0), (4, 4 ,1), (5, 3 ,0)

;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY ID) n from @t
), NumbersWithout as (
    select
        n,
        ROW_NUMBER() OVER (ORDER BY n) as rn
    from
        Numbers
    where n not in (select ID from @t where IsPinned=1)
), DataWithout as (
    select
        *,
        ROW_NUMBER() OVER (ORDER BY GMD desc) as rn
    from
        @t
    where
        IsPinned = 0
)
select
    t.*,
    COALESCE(nw.n,t.ID) as RowNo
from
    @t t
        left join
    DataWithout dw
        inner join
    NumbersWithout nw
        on
            dw.rn = nw.rn
        on
            dw.ID = t.ID
order by COALESCE(nw.n,t.ID)

希望我的命名能够清楚地表明我们在做什么。决赛我有点厚颜无耻SELECT通过使用 COALESCE得到最后的RowNo当您可能期望 CASE表达。但它有效，因为 DataWithout 的内容CTE 被定义为仅存在于未固定的项目中，这使得最终的 LEFT JOIN失败。

结果:

ID          GMD                                     IsPinned RowNo
----------- --------------------------------------- -------- --------------------
5           3.00                                    0        1
2           0.00                                    1        2
1           2.50                                    0        3
4           4.00                                    1        4
3           2.00                                    0        5

---

可能表现更好的第二种变体(但永远不要假设，总是测试):

declare @t table (ID int,GMD decimal(5,2),IsPinned bit)
insert into @t (ID,GMD,IsPinned) values
(1,2.5,0), (2, 0 ,1), (3, 2 ,0), (4, 4 ,1), (5, 3 ,0)

;With Numbers as (
    select ROW_NUMBER() OVER (ORDER BY ID) n from @t
), NumbersWithout as (
    select
        n,
        ROW_NUMBER() OVER (ORDER BY n) as rn
    from
        Numbers
    where n not in (select ID from @t where IsPinned=1)
), DataPartitioned as (
    select
        *,
        ROW_NUMBER() OVER (PARTITION BY IsPinned ORDER BY GMD desc) as rn
    from
        @t
)
select
    dp.ID,dp.GMD,dp.IsPinned,
    CASE WHEN IsPinned = 1 THEN ID ELSE nw.n END as RowNo
from
    DataPartitioned dp
        left join
    NumbersWithout nw
        on
            dp.rn = nw.rn
order by RowNo

在第三个 CTE 中，通过引入 PARTITION BY并删除 WHERE子句我们确保我们拥有所有数据行，因此我们不需要在这个变体的最终结果中重新连接到原始表。