与“相关”子查询的有效联接

与“相关”子查询的有效联接

试试这个：

SELECt  rt.rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qtyFROM    (    SELECt  *    FROM    (        SELECt  r.*, t.*, SUM(doUse) OVER (PARTITION BY rangeId ORDER BY aDate) AS span        FROM    ( SELECt  r.rangeId, startDate, MAX(day) AS dm FROM    Range r, Days d WHERe   d.rangeid = r.rangeid GROUP BY     r.rangeId, startDate ) r, Dates t        WHERe   t.adate >= startDate        ORDER BY rangeId, t.adate        )    WHERe        span <= dm    ) rt, Days dWHERe   d.rangeId = rt.rangeID    AND d.day = GREATEST(rt.span, 1)

Dates

附言：在我看来，将所有这些保留在数据库中的唯一目的是获得一个带有假日标记的连续日历。

您可以使用以下构造在Oracle中生成任意长度的日历：

SELECt :startDate + ROWNUMFROM   dualConNECT BY       1 = 1WHERe  rownum < :length

并只在放假

Dates

。一个简单的联接将向您显示哪些

Dates

假期和哪些假期。