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

admin

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

sql

给定Oracle中的三个表Dates（日期aDate，doUse布尔值），Days（rangeId整数，day整数，qty整数）和Range（rangeId整数，startDate日期）

我想加入这些，以便将范围与aDate = startDate中的日期（其中doUse = 1，每天在Days中）结合在一起。

给定一个范围，可能会执行以下操作

SELECT rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM (
    SELECT aDate, doUse, SUM(doUse) OVER (ORDER BY aDate) day
    FROM Dates 
    WHERE aDate >= :startDAte
) INNER JOIN (
    SELECT rangeId, day,qty
    FROM Days
    WHERE rangeId = :rangeId
) USING (day)
ORDER BY day ASC

我想做的是查询范围中的所有范围，而不仅仅是一个。

问题在于联接值“ day”取决于要计算的范围startDate，这给我编写查询带来了一些麻烦。

请记住，“日期”表非常庞大，因此我希望避免从表中的第一个日期开始计算日期值，而每个“范围天”不应超过100天左右。

编辑：样本数据

Dates                            Days
aDate        doUse               rangeId     day     qty
2008-01-01   1                   1           1       1
2008-01-02   1                   1           2       10
2008-01-03   0                   1           3       8
2008-01-04   1                   2           1       2
2008-01-05   1                   2           2       5

Ranges
rangeId      startDate
1            2008-01-02
2            2008-01-03


Result
rangeId      aDate        qty
1            2008-01-02   1
1            2008-01-03   0
1            2008-01-04   10
1            2008-01-05   8
2            2008-01-03   0
2            2008-01-04   2
2            2008-01-05   5

阅读 248

2021-07-01

共1个答案

admin

试试这个：

SELECT  rt.rangeId, aDate, CASE WHEN doUse = 1 THEN qty ELSE 0 END AS qty
FROM    (
    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 d
WHERE   d.rangeId = rt.rangeID
    AND d.day = GREATEST(rt.span, 1)

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

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

SELECT :startDate + ROWNUM
FROM   dual
CONNECT BY
       1 = 1
WHERE  rownum < :length

并只在放假Dates。一个简单的联接将向您显示哪些Dates假期和哪些假期。

2021-07-01