重置row_number（）在另一列中看到1时

Question

说我有这样的桌子。

| Date       | Condition |
|------------|-----------|
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 1         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-25 | 0         |
| 2021-12-25 | 0         |
| 2021-12-25 | 0         |

我想将row_number添加到它，但是当它看到iscon = 1或更改的日期时，我希望将其重置为1，所以它就是这样。

| Date       | Condition | row_number |
|------------|-----------|------------|
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-24 | 0         | 4          |
| 2021-12-24 | 1         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-25 | 0         | 1          |
| 2021-12-25 | 0         | 2          |
| 2021-12-25 | 0         | 3          |

我尝试了

    WITH cte AS
(
    SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY [isCon],[date],[grp] ORDER BY [date]) AS rn
    FROM (select t.*,
             (  
                row_number() over (order by date) -
                row_number() over (partition by isCon,date order by date)
              ) as grp
            from @myTablevariable t
          ) t
    
)
SELECT * FROM cte;

它很接近，但不太正确。 ROW_NUMBER重置为1，无论看到0或1，我都希望它仅在看到1时重置。

| Date       | Condition | row_number |
|------------|-----------|------------|
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-24 | 0         | 4          |
| 2021-12-24 | 1         | 1          |
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-25 | 0         | 1          |
| 2021-12-25 | 0         | 2          |
| 2021-12-25 | 0         | 3          |

请注意，我在SQL Server 2008上，我不可能升级。因此，诸如lag（）函数或sum> sum（column1）之类的东西（顺序by）对我来说是不可行的。

Answer 1

正如我在评论中提到的那样，您需要在此处 订购列；如果您没有一个，那么如果不添加上述列（故事的结尾），那是不可能的。

假设您确实有一列可以通过订购这只是一个差距和岛屿问题：

WITH CTE AS(
    SELECT YourAlwaysAscendingColumn,
           [date],
           Condition,
           ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY YourAlwaysAscendingColumn) - 
           ROW_NUMBER() OVER (PARTITION BY [Date], Condition ORDER BY YourAlwaysAscendingColumn) AS Grp
    FROM dbo.YourTable)
SELECT [date],
       Condition,
       ROW_NUMBER() OVER (PARTITION BY [Date], Grp ORDER BY YourAlwaysAscendingColumn) AS RowNumber
FROM CTE
ORDER BY [date],
         YourAlwaysAscendingColumn;

db＆lt;＆gt; fiddle

Answer 2

也许这样？

DECLARE
    @TestData TABLE
    (
        SomeDate DATE,
        Condition INT
    );

INSERT INTO @TestData
VALUES
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 1),
('20211224', 0),
('20211224', 0),
('20211225', 0),
('20211225', 0),
('20211225', 0)

SELECT
    SomeDate,
    Condition,
    ROW_NUMBER() OVER (PARTITION BY SomeDate, Condition ORDER BY (SELECT 0))
FROM
    @TestData;

（选择0）可以是任何常数，例如（选择'nothtings'）