sql - 条件行号

Question

我试图根据 RowNum 列的结果进行条件编号。

当 RowNum 为 1 时，我想要新的列，全新增量为 1。

在图中，RoomNum 5 列应替换为 2、9 替换为 3m、13 替换为 4，等等。我在此查询中做错了什么？

SELECT  CASE 
        WHEN rownum < 2
    THEN
    Row_number() OVER (
        PARTITION BY Scheme ORDER BY Scheme ASC
        )  
    ELSE NULL
END AS RoomNum,

CASE 
    WHEN rownum > 1
        THEN NULL
    ELSE scheme
    END AS Scheme
,RowNum

Answer 1

您需要根据RoomNm是否为NULL进行分区。结果值也会有一个CASE:

select (case when roomnum is not null
             then row_number() over (partition by scheme, (case when roomnum is not null then 1 else 0 end)
                                     order by roomnum
                                    )
        end) as RoomNum