您可以尝试以下方法，这里是演示。我假设 id 将始终具有连续值。

with cte as
(
  select
    *,
    count(*) over (partition by IsTrue, rnk) as total
  from
  (
      select
          *,
          id - row_number() over (partition by IsTrue order by id, date) as rnk
      from myTable
  ) val
)

select
    accountId,
    min(date) as start,
    max(date) as end
from cte
where total > 1
group by
    accountId,
    rnk

输出：

| accountid | start      | end        |
| --------- | ---------- | -----------|
| 1         | 2013-01-01 | 2013-01-03 |
| 1         | 2013-01-07 | 2013-01-09 |

这是一个缺口和孤岛问题的例子。对于这个版本，您只需要为每个 isTrue . 对于相同的相邻值，从每个日期减去天数是一个常量：

select accountId, isTrue, min(date), max(date)
from (select t.*,
             row_number() over (partition by accountId, isTrue order by date) as seqnum
      from t
     ) t
group by accountId, isTrue, dateadd(day, -seqnum, date);

这定义了所有组。如果我假设您只需要大于1天的值“1”，那么：

select accountId, isTrue, min(date), max(date)
from (select t.*,
             row_number() over (partition by accountId, isTrue order by date) as seqnum
      from t
      where isTrue = 1
     ) t
group by accountId, isTrue, dateadd(day, -seqnum, date)
having count(*) > 1;