ROW_Number自定义组

Question

我正在尝试基于自定义分组进行row_number，但我无法生成它。

以下是我的查询

CREATE TABLE mytbl (wid INT, id INT)
INSERT INTO mytbl Values(1,1),(2,1),(3,0),(4,2),(5,3)

电流输出

wid     id
1        1
2        1
3        0
4        2
5        3

查询

SELECT *, RANK() OVER(PARTITION BY wid, CASE WHEN id = 0 THEN 0 ELSE 1 END ORDER BY ID)
FROM mytbl

我想根据自定义条件对行进行排名，例如如果 ID 为 0，那么我开始新组，直到我没有 0 ID。

预期输出

wid     id  RN
1        1  1
2        1  1
3        0  1
4        2  2
5        3  2

Answer 1

在这里猜测，因为我们没有太多的澄清，但也许是这个：

SELECT wid,
id,
COUNT(CASE id WHEN 0 THEN 1 END) OVER (ORDER BY wid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +1 AS [Rank]
FROM mytbl ;

Answer 2

如果我理解正确，您可以使用下一个方法。请注意，您需要有一个排序列(我假设这是wid列)：

陈述：

;WITH ChangesCTE AS (
SELECT 
*,
CASE WHEN LAG(id) OVER (ORDER BY wid) = 0 THEN 1 ELSE 0 END AS ChangeIndex
FROM mytbl
), GroupsCTE AS (
SELECT 
*,
SUM(ChangeIndex) OVER (ORDER BY wid) AS GroupIndex
FROM ChangesCTE
)
SELECT 
wid,
id,
DENSE_RANK() OVER (ORDER BY GroupIndex) AS Rank
FROM GroupsCTE

结果：

wid id  Rank
1   1   1
2   1   1
3   0   1
4   2   2
5   3   2

Answer 3

没有对所需的逻辑进行太多澄清，我的理解是您希望在id = 0时将Rank增加 1

select  wid, id, 
[Rank]  = sum(case when id = 0 then 1 else 0 end) over(order by wid) 
+ case when id <> 0 then 1 else 0 end
from    mytbl

Answer 4

试试这个，

CREATE TABLE #mytbl (wid INT, id INT)
INSERT INTO #mytbl Values(1,1),(2,1),(3,0)
,(4,2),(5,3),(6,0),(7,4),(8,5),(9,6)
;with CTE as
(
select *,ROW_NUMBER()over(order by wid)rn 
from #mytbl where id=0
)
,CTE1 as
(
select max(rn)+1 ExtraRN from CTE
)
select a.* ,isnull(ca.rn,ca1.ExtraRN) from #mytbl a
outer apply(select top 1 * from CTE b 
where a.wid<=b.wid )ca
cross apply(select ExtraRN from CTE1)ca1

drop table #mytbl

在这里，OUTER APPLY和CROSS APPLY都不会增加cardianility估计。它将始终只返回一行。