这是一个“孤岛”问题。
declare @Yourtable table
(SecID int, Date Date, Price int)
INSERT INTO @Yourtable
SELECT 1,GETDATE()-5,10 union all
SELECT 1,GETDATE()-4,10 union all
SELECT 1,GETDATE()-3,5 union all
SELECT 1,GETDATE()-2,10 union all
SELECT 1,GETDATE()-1, 10
;WITH cte AS
(
SELECT SecID,Date,Price,
ROW_NUMBER() OVER (PARTITION BY SecID ORDER BY Date) -
ROW_NUMBER() OVER (PARTITION BY Price, SecID ORDER BY Date) AS Grp
FROM @Yourtable
)
SELECT SecID,Price, MIN(Date) StartDate, MAX(Date) EndDate
FROM cte
GROUP BY SecID, Grp, Price
ORDER BY SecID, MIN(Date)