有效期的SQL处理之生效日期--转自 老颜博客

这种设计使用起来比较简单了，这里列两种方式：

1.方法一例子

SELECT *
  FROM (SELECT to_date(’2009-07-01′, ‘yyyy-mm-dd’) + LEVEL – 1 AS flight_dt
          FROM dual
        CONNECT BY LEVEL <= to_date(’2009-07-31′, ‘yyyy-mm-dd’) -
                   to_date(’2009-07-01′, ‘yyyy-mm-dd’) + 1) k,
       (SELECT t.*,
               lead(apply_date) over(PARTITION BY ORI_ENG, DES_ENG, AC_FAMILY_CD ORDER BY apply_date) – 1 next_apply_date
          FROM sa_public_fund t) m
WHERE k.flight_dt BETWEEN m.apply_date AND
       nvl(next_apply_date, to_date(’2099-12-31′, ‘yyyy-mm-dd’))

2.方法二例子

SELECT ac_no, company_cd, apply_dt, MIN(next_apply_dt) – 1
  FROM (SELECT a.*, b.apply_dt AS next_apply_dt
          FROM sa_aircraft a
          LEFT JOIN sa_aircraft b
            ON a.ac_no = b.ac_no
           AND a.apply_dt < b.apply_dt)
GROUP BY ac_no, company_cd, apply_dt