我有两个表:

Dates:

  |ReportHeader|
  --------------
  |     2015-04|
  |     2015-05|
  |     2015-06|
Data:
  |ReportHeader|Customer|Sales|
  -----------------------------
  |     2015-04|    Plop|  684|
  |     2015-06|    Plop|  486|

I have my query:

select
    Dates.ReportHeader,
    Data.Customer,
    Data.Sales
from Dates
    left outer join data on Dates.ReportHeader = Data.ReportHeader

我期望返回的是:

  |ReportHeader|Customer|Sales|
  -----------------------------
  |     2015-04|    Plop|  684|
  |     2015-05|    null| null|
  |     2015-06|    Plop|  486|

The results that I am getting are:

  |ReportHeader|Customer|Sales|
  -----------------------------
  |     2015-04|    Plop|  684|
  |     2015-06|    Plop|  486|

Is there any reason to be found why this left join would behave like an inner join? Or do I just not understand how a left join is supposed to work.

The Oracle version i am using is: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Thanks in advance for any help extended

edit: It seems i have over simplified the problem so i better put the whole query here:

WITH
    L0 AS (SELECT 1 C from dual UNION ALL SELECT 1 O from dual),
    L1 AS (SELECT 1 C FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 C FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 C FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 C FROM L3 A CROSS JOIN L3 B),
    Nums AS (SELECT 0 N FROM dual union SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT NULL from dual) ) N FROM L4),
    Dates as (
      select distinct
        to_char(to_date(last_day(current_timestamp + (-1 * N))) , 'yyyy-MM') as ReportHeader,
        to_date(add_months(last_day(current_timestamp + (-1 * N)),-1)+1) as FirstDayOfMonth,
        to_date(current_timestamp + (-1 * N))                               as ReportDate,
        to_date(last_day(current_timestamp + (-1 * N)))                     as LastDayOfMonth
      from Nums
      where N <= 3 * 365
  ), Invoices AS (
    select
      TP.ID,
      TP.Name,
      h.FORMATTEDINVOICENUMBER,
      l.SCHEDULEID,
      to_date(h.INVOICEDUEDATE) as date_due,
      to_date(L.EFFECTIVEDATEFULLYPAID) as date_payed
    from  ODSTHIRDPARTY TP
      LEFT JOIN ODSINVOICELINE L on L.INVCUSID = TP.ID
      LEFT JOIN ODSINVOICEHEADER H  on h.ID = l.INVOICEHEADERID
    where TP.NAME not in ('<None>','<Unknown>')
), data as (
    select distinct
      Invoices.Name as Customer,
      Dates.ReportHeader,
      count(distinct Invoices.SCHEDULEID) over (partition by Dates.ReportHeader, Invoices.Name) as NumContracts,
      floor(avg(
        case
          when Invoices.date_due > Dates.LastDayOfMonth THEN 0
          when coalesce(Invoices.date_payed, current_date) < Dates.FirstDayOfMonth THEN 0
          when Invoices.date_payed < Invoices.date_due then Invoices.date_payed - Invoices.date_due
          else to_date(least(coalesce(Invoices.date_payed, current_date), Dates.LastDayOfMonth )) - to_date(Invoices.date_due)
        end
      ) over (partition by Dates.ReportHeader, Invoices.Name))  past_due,
      Dates.FirstDayOfMonth as ReportStartDate,
      Dates.LastDayOfMonth  as ReportEndDate,
      coalesce(Invoices.date_payed, current_date) as calc_date
    from Dates
      left join Invoices on Dates.ReportDate between Invoices.date_due and coalesce(Invoices.date_payed, current_date)
    where coalesce(Invoices.date_payed, current_date) >= Invoices.date_due
)
    select distinct
      Dates.ReportHeader,
      data.Customer,
      data.NumContracts,
      data.past_due
    from Dates
      left join data on data.ReportHeader = Dates.ReportHeader
 order by data.Customer,dates.ReportHeader

比较麻烦的是最后的查询。 从加入数据的剩余日期中选择

对于那些感兴趣的人，正如@APC 在评论中所说的那样。创建一个可测试的用例。这样做促使我重写现在有效的完整查询。我最初的前提是错误的，我采用了错误的初始方法。

修改后的查询如下:

WITH
    L0 AS (SELECT 1 C from dual UNION ALL SELECT 1 O from dual),
    L1 AS (SELECT 1 C FROM L0 A CROSS JOIN L0 B),
    L2 AS (SELECT 1 C FROM L1 A CROSS JOIN L1 B),
    L3 AS (SELECT 1 C FROM L2 A CROSS JOIN L2 B),
    L4 AS (SELECT 1 C FROM L3 A CROSS JOIN L3 B),
    Nums AS (SELECT 0 N FROM dual union SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT NULL from dual) ) N FROM L4),
    Dates as (
      select distinct
        to_char(to_date(last_day(current_timestamp + (-1 * N))), 'yyyy-MM') as ReportHeader,
        to_date(add_months(last_day(current_timestamp + (-1 * N)), -1) + 1) as FirstDayOfMonth,
        to_date(current_timestamp + (-1 * N))                               as ReportDate,
        to_date(last_day(current_timestamp + (-1 * N)))                     as LastDayOfMonth
      from Nums
      where N <= 2 * 365
  ), ThirdParties as (
    select distinct
      TP.ID,
      TP.Name,
      Dates.ReportHeader,
      Dates.FirstDayOfMonth,
      Dates.ReportDate,
      Dates.LastDayOfMonth
    from ODSTHIRDPARTY TP
    cross join Dates
    where TP.NAME not in ('<None>','<Unknown>')
  ), Invoices AS (
    select distinct
      TP.ID,
      TP.Name,
      TP.ReportHeader,
      TP.FirstDayOfMonth,
      OH.FORMATTEDINVOICENUMBER,
      TP.LastDayOfMonth,
      il.SCHEDULEID,
      to_date(il.invoiceReferenceDate)                          as date_due,
      to_date(iL.EFFECTIVEDATEFULLYPAID)                  as date_payed,
      case
        when il.id is null then 0
        else
          case
            when to_date(il.invoiceReferenceDate) <= to_date(coalesce(iL.EFFECTIVEDATEFULLYPAID,least(current_date,tp.LastDayOfMonth))) then
              to_date(coalesce(iL.EFFECTIVEDATEFULLYPAID,least(current_date,tp.LastDayOfMonth))) - to_date(il.invoiceReferenceDate)
            else 0
          end
      end as daysLate
    from  ThirdParties TP
      LEFT JOIN ODSINVOICELINE IL on IL.INVCUSID = TP.ID and TP.ReportDate
                            between il.invoiceReferenceDate and coalesce(il.EFFECTIVEDATEFULLYPAID, current_date)
      left join ODSINVOICEHEADER OH on IL.INVOICEHEADERID = OH.ID
), data as (
    select distinct
      Invoices.ReportHeader,
      Invoices.Name                                                                       as Customer,
      count(distinct Invoices.SCHEDULEID) over (partition by Invoices.ReportHeader, Invoices.Name) as NumContracts,
      floor(avg(
        case
          when Invoices.date_due > Invoices.LastDayOfMonth THEN 0
          when coalesce(Invoices.date_payed, current_date) < Invoices.FirstDayOfMonth THEN 0
          when Invoices.date_payed < Invoices.date_due then Invoices.date_payed - Invoices.date_due
          else to_date(least(coalesce(Invoices.date_payed, current_date), Invoices.LastDayOfMonth )) - to_date(Invoices.date_due)
        end
      ) over (partition by Invoices.ReportHeader, Invoices.Name))  past_due,
      Invoices.FirstDayOfMonth                                                               as ReportStartDate,
      Invoices.LastDayOfMonth                                                                as ReportEndDate,
      coalesce(Invoices.date_payed, current_date)                                         as calc_date
    from Invoices
                           -- and coalesce(Invoices.date_payed, current_date) >= Invoices.date_due
    order by Invoices.Name, Invoices.reportheader
), upvt as (
    select distinct
      row_number()
      over (
        partition by customer
        order by ReportHeader ) as ColNum,
      data.ReportHeader            ColName,
      data.Customer,
      data.NumContracts,
      data.past_due
    from data
), pvt as (
  select * from (
    select Customer, ColName, ColNum, NumContracts, past_due from upvt
  ) pivot (
    MAX(past_due) as DueDays, MAX(NumContracts) as Contracts, Max(ColName) as ColName
                for ColNum in ( '1', '2', '3', '4', '5', '6', '7', '8', '9','10','11','12','13','14','15',
                                       '16','17','18','19','20','21','22','23','24')) pvt
)
select
  row_number() over (order by Customer) as No,
  pvt.*
from pvt;