計算一年中存在的記錄，直到其結束日期為止已發布的每一年

幾周前我問了一個問題，我以為我問的是正確的問題，但我需要修改它。我正在發布一個新問題，但是可以在此處找到之前的帖子感謝@Serg 對我之前的問題的快速回復。

我已經復制并粘貼了下面的問題。對我的問題的修改是，我假設結束日期是 sysdate，而我實際上有一個 END_DATE 列可以使用。因此，我不想計算每年到 sysdate 之前都處于活動狀態的所有檔案，而是要計算每年到 END_DATE 之前都處于活動狀態的所有檔案。

我之前的問題針對我的新問題進行了編輯：

我有一個包含許多活動檔案的 Oracle 表。每行都是一個檔案，并具有唯一的檔案#、發布日期和結束日期

我想查詢在發布年份和結束日期之間每年有多少檔案處于活動狀態。因此，如果它是在 2010 年發布的，那么我想將該檔案包含在自發布以來的每一年（2010、2011、2012 年等）中，直到其結束日期。

我希望我的茶幾看起來像：

uj5u.com熱心網友回復：

只需使用簡單的生成器：

第 1 步：獲取每條記錄的所有年份：

with 
t(File#, ISSUE_DATE, END_DATE) as (
    select 1254, to_date('15-OCT-1997','dd-mon-yyyy'),to_date('11-NOV-2005','dd-mon-yyyy') from dual union all
    select 5245, to_date('22-MAY-2005','dd-mon-yyyy'),to_date('15-OCT-2008','dd-mon-yyyy') from dual union all
    select 7852, to_date('02-APR-2015','dd-mon-yyyy'),to_date('05-DEC-2023','dd-mon-yyyy') from dual union all
    select 9852, to_date('11-MAR-2021','dd-mon-yyyy'),to_date('22-OCT-2028','dd-mon-yyyy') from dual
)
select
     t.*, yyyy
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years

這將回傳：

     FILE# ISSUE_DATE          END_DATE                  YYYY
---------- ------------------- ------------------- ----------
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1997
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1998
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1999
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2000
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2001
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2002
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2003
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2004
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2005
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2005
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2006
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2007
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2008
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2015
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2016
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2017
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2018
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2019
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2020
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2021
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2022
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2023
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2021
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2022
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2023
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2024
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2025
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2026
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2027
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2028

2. 然后將它們聚合起來：

select
    yyyy, count(*) cnt
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years
group by yyyy;

帶有測驗資料的完整測驗用例：

with 
t(File#, ISSUE_DATE, END_DATE) as (
    select 1254, to_date('15-OCT-1997','dd-mon-yyyy'),to_date('11-NOV-2005','dd-mon-yyyy') from dual union all
    select 5245, to_date('22-MAY-2005','dd-mon-yyyy'),to_date('15-OCT-2008','dd-mon-yyyy') from dual union all
    select 7852, to_date('02-APR-2015','dd-mon-yyyy'),to_date('05-DEC-2023','dd-mon-yyyy') from dual union all
    select 9852, to_date('11-MAR-2021','dd-mon-yyyy'),to_date('22-OCT-2028','dd-mon-yyyy') from dual
)
select
    yyyy, count(*) cnt
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years
group by yyyy
order by yyyy;

輸出：

      YYYY        CNT
---------- ----------
      1997          1
      1998          1
      1999          1
      2000          1
      2001          1
      2002          1
      2003          1
      2004          1
      2005          2
      2006          1
      2007          1
      2008          1
      2015          1
      2016          1
      2017          1
      2018          1
      2019          1
      2020          1
      2021          2
      2022          2
      2023          2
      2024          1
      2025          1
      2026          1
      2027          1
      2028          1

標籤：sql 甲骨文

上一篇：Djangowagitail后端oracle遷移ORA-00907：缺少右括號

下一篇：SELECTINTO陳述句在SQL程序中拋出錯誤