这就是缺口和孤岛问题。您可以使用row\u numer和group by进行此操作。

select  t.stock, min(transaction_date), max(transaction_date)
from (
   select row_number() over (order by transaction_date) -
      row_number() over (partition by stock order by transaction_date) grp,
      transaction_date,
      stock
   from data
) t
group by t.grp, t.stock

在下面的dbfiddle演示中，我还解决了 null 最后一个组的值，但查找连续行的主要思想是建立在上述查询的基础上。
你可以检查这个来解释这个解决方案。

尝试使用 GROUP BY 与 MIN 以及 MAX :

SELECT
  stock,
  MIN(transaction_date) Start_date,
  CASE WHEN COUNT(*)>1 THEN MAX(transaction_date) END end_date      
FROM Sample_table
GROUP BY stock
ORDER BY stock

您可以尝试使用lead、lag函数，如下所示：

select currentStockDate as startDate, 
LEAD(currentStockDate,1) as EndDate,
currentStock
from
(select *
from
(select 
LAG(transaction_date,1) over(order by transaction_date) as prevStockDate,
 transaction_date as CurrentstockDate,
LAG(stock,1) over(order by transaction_date) as prevStock,
stock as currentStock
from sample_table) as t
where (prevStock <> currentStock) or (prevStock is null)
) as t2

你可以在下面使用 row_number() ```
select stock,min(transaction_date) as start_date,
case when min(transaction_date)=max(transaction_date) then null else max(transaction_date) end as end_date
from
(
select *,row_number() over(order by transaction_date)-
row_number() over(partition by stock order by transaction_date) as rn
from t1
)A group by stock,rn