更新时间:2023-01-24 15:46:53
假设使用SQL Server,下面是一个示例:
Assuming SQL Server, here is an example:
DECLARE @T table (id int, [month] char(3), flag bit)
INSERT INTO @T
VALUES
(111, 'jan', 1)
,(111, 'feb', 1)
,(111, 'mar', 1)
,(111, 'apr', 0)
,(111, 'may', 0)
,(111, 'jun', 1)
,(222, 'jan', 1)
,(222, 'feb', 1)
,(222, 'mar', 0)
,(222, 'apr', 0)
,(222, 'may', 0)
,(222, 'jun', 1)
SELECT
id
, [month]
, flag
, ROW_NUMBER() OVER (PARTITION BY id, section ORDER BY monthNum) [order]
FROM
(
SELECT
id
, [month]
, monthNum
, flag
, SUM(CASE WHEN newValue = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY monthNum) section
FROM
(
SELECT
id
, [month]
, monthNum
, flag
, CASE WHEN LAG(flag, 1, ABS(flag - 1)) OVER (PARTITION BY id ORDER BY monthNum) = flag THEN 0 ELSE 1 END newValue
FROM
(
SELECT
id
, [month]
, MONTH(CAST('1 ' + [month] + ' 17' AS datetime)) monthNum
, flag
FROM @T
) Q
) Q2
) Q3