代码之家 › 专栏 › 技术社区 › Brandon

如何获得给定条件的连续行

common-table-expression sql-server sql

Brandon · 技术社区 · 6 年前

我有一张这样的桌子

    WeekId     PassesCondition
----------------------------------
      1              1
      2              0
      3              0
      4              1
      5              1
      6              1

我需要写一个查询,从最大值开始计算连续几周 WeekId 条件已经通过。

我已经做了一些搜索,我似乎需要用 CTE 和 ROW_NUMBER() 可能。我尝试过一些东西,但结果甚至不值得作为“我迄今为止尝试过的东西”发表。

输出应该只是从 Max(WeekId) 情况已经过去了。例如1、2、5等。

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

假设条件是 0 或 1 :

select count(*)
from t
where t.weekid > (select max(t2.weekid) from t t2 where t2.passesCondition = 0) or
      not exists (select 1 from t t2 where t2.passesCondition = 0);

如果你知道 passesCondition = 0 存在.

如果你喜欢使用窗口函数,你也可以用累积的反向MIN来完成:

select count(*)
from (select t.*,
             min(passesCondition) over (order by weekid desc) as running_min
      from t
     ) t
where running_min = 1;

或者, not exists 可以使用:

select count(*)
from t
where not exists (select 1
                  from t t2
                  where t2.weekid > t.weekid and t2.passesCondition = 0
                 );

或 > all :

select count(*)
from t
where t.weekid > all (select t2.weekid from t t2 where t2.passesCondition = 0);

我没有意识到有这么多不同的方式来表达这一点。毫无疑问,还有很多。

Ross Bush 6 年前

如果你需要考虑多个开始和停止,你可以使用类似于以下的查询来计数岛屿:

DECLARE @T TABLE(WeekId INT, PassedCondition BIT)
INSERT @T VALUES (1,1),(2,0),(3,0),(4,1),(5,1),(6,1),(7,1),(8,0),(9,0),(10,1)

;WITH Holes AS(SELECT WeekID FROM @T WHERE PassedCondition=0),
SegmentStart AS
(
    SELECT WeekId, HoleWeekId 
    FROM
    (
        SELECT  This.WeekId, HoleWeekId = MIN(H.WeekId)
        FROM  @T This
        INNER  JOIN Holes H ON H.WeekId > This.WeekID AND PassedCondition=1
        GROUP BY This.WeekId
        UNION 
        SELECT WeekId = MAX(WeekID), HoleWeekId=MAX(WeekID)+1 
        FROM @T 
        WHERE PassedCondition=1
    )AS X
)
SELECT 
    WeekSegmentEnd = HoleWeekId-1,
    ConsecutiveCount = COUNT(*)
FROM 
    SegmentStart
GROUP BY
    HoleWeekId

forpas 6 年前

计算 最大限度 weekid 那有 passesCondition = 0 (独家)和 最大限度 周日 (排他性):

SELECT COUNT(*)
FROM tablename t
WHERE 
  (t.weekid < (SELECT MAX(weekid) FROM tablename))
  AND
  (t.weekid > (SELECT MAX(weekid) FROM tablename WHERE passesCondition = 0));

如果你想包括最后一个 周日 你改变 < 到 <= .