将if语句与row_number一起使用，然后使用

Question

我已经尝试创建下面的代码，但对我不起作用。

我要做的是使用row_number，但是请检查列MP ='yes'，然后+1 to row_number值，如果它确实可以给我row_number。

  SELECT
 [Key]
,[Key2]
,MP
,OriginalOrder = ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])
,IIF(MP = 'Yes' , ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2])+1,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]))what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2

Answer 1

如果我正确理解，则可以尝试使用sum window>在时使用时，如果mp是YES是添加一个。

SELECT
     [Key]
    ,[Key2]
    ,MP
    ,ROW_NUMBER() OVER(Partition by [Key] ORDER BY  [Key],[Key2]) OriginalOrder 
    ,SUM(CASE WHEN MP = 'Yes' THEN 2 ELSE 1 END) OVER(Partition by [Key] ORDER BY  [Key],[Key2]) what_i_have_tried
FROM
cte_RowNumbertest
ORDER BY
1,2

Answer 2

i think 您实际想要的是row_number和条件运行count：

 SELECT [Key]
        [Key2],
        MP,
        ROW_NUMBER() OVER (Partition by [Key] ORDER BY [Key],[Key2]) AS OriginalOrder,
        ROW_NUMBER() OVER (Partition by [Key] ORDER BY [Key],[Key2]) +
        COUNT(CASE MP WHEN 'Yes' THEN 1 END) OVER (Partition by [Key] ORDER BY [Key],[Key2]) AS ExpectedOutput
FROM dbo.YourTable
ORDER BY [Key]
         [Key2];