这是一个主意（和它有关）：
;WITH MoneyComingIn AS( SELECt MIN(startdate) AS startdate, MAX(enddate) AS enddate, SUM(amount) AS amount FROM myTable WHERe amount > 0)SELECt MIN(startdate) AS startdate, MAX(enddate) AS enddate, SUM(amount) AS amountFROM myTableWHERe enddate < (SELECt startdate FROM MoneyComingIn)UNIOn ALLSELECt startdate, enddate, amountFROM MoneyComingInUNIOn ALLSELECt MIN(startdate) AS startdate, MAX(enddate) AS enddate, SUM(amount) AS amountFROM myTableWHERe startdate > (SELECt enddate FROM MoneyComingIn)
第二秒，不使用
UNIOn（fiddle）：
SELECt MIN(startdate), MAX(enddate), SUM(amount)FROM( SELECt startdate, enddate, amount, CASE WHEN EXISTS(SELECT 1 FROM myTable b WHERe b.id>=a.id AND b.amount > 0) THEN CASE WHEN EXISTS(SELECt 1 FROM myTable b WHERe b.id<=a.id AND b.amount > 0) THEN 2 ELSE 1 END ELSE 3 END AS partition_no FROM myTable a) xGROUP BY partition_no
尽管我认为如所写，但它假定
Id是有序的。您可以用代替它
ROW_NUMBER() OVER(ORDER BY startdate)。
