提问人：Huss1 提问时间：4/22/2022 最后编辑：Huss1 更新时间：4/22/2022 访问量：400
使用带有 Row_Number then +1 的 If 语句
Using If statement with a Row_Number then +1
问：
我尝试创建下面的代码，但它对我不起作用。
我想做的是使用 Row_number但是检查列 MP =' Yes' 然后 +1 到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
答：
2赞
D-Shih
4/22/2022
#1
如果我理解正确，您可以尝试使用窗口功能，如果是，请再添加一个。SUMCASE WHENMP
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
1赞
Thom A
4/22/2022
#2
我认为你真正想要的是一个和条件运行：ROW_NUMBERCOUNT
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];
评论
0赞
Huss1
4/22/2022
这也奏效了，非常感谢Larnu
评论