在T-SQL中按日期范围分组

在T-SQL中按日期范围分组

这是一个主意（和它有关）：

;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)

。