sql - Oracle left join 表现得像 inner join-6ren

sql - Oracle left join 表现得像 inner join

转载作者：行者123 更新时间：2023-12-05 08:11:48

27

4

<分区>

我有两个表:

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;

27

4

0

文章推荐： java - 如何在 GraphQL Java 上拆分模式

文章推荐： python - 在 spark-submit 之前安装 pyspark 作业的要求

文章推荐： git - 从 GitLab merge 请求中排除特定提交

join - 从一个“join”表到另一个“join”表的SqlAlchemy关系
我正在测试设置SQLAlchemy以映射现有数据库。这个数据库是很久以前自动建立的，它是由我们不再使用的先前的第三方应用程序创建的，因此 undefined 某些预期的事情，例如外键约束。该软件将管理
mysql - INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
这个问题在这里已经有了答案: What is the difference between "INNER JOIN" and "OUTER JOIN"? (28 个答案) 关闭 7 年前。 INNE
mysql - INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
这个问题在这里已经有了答案: What is the difference between "INNER JOIN" and "OUTER JOIN"? (29 个回答) 关闭7年前. INNER J
join - Hive:LEFT JOIN 与 JOIN 在 ON 子句中使用过滤器给出不同的结果
假设有两个表: table1.c1 table1.c2 1 1 A 2 1 B 3 1 C 4 2
join - Hive:LEFT JOIN 与 JOIN 在 ON 子句中使用过滤器给出不同的结果
假设有两个表: table1.c1 table1.c2 1 1 A 2 1 B 3 1 C 4 2
数据库Left join , Right Join, Inner Join 的相关内容，非常实用
一.先看一些最简单的例子例子 Table A aid adate 1 a1 2&nb
SQL 外链接操作小结 inner join left join right join
数据库操作语句 7. 外连接——交叉查询 7.1 查询 7.2 等值连接 7.3 右外
ruby-on-rails - :joins | change behavior inner join to left join
我有两个表 'users' 和 'lms_users' class LmsUser belongs_to :user end class User has_one :lms_user
ruby-on-rails - 首先使用 `joins()` 进行 INNER JOIN 然后是下一个表的 LEFT JOIN
我试图避免在 Rails 中对我的 joins 进行字符串插值，因为我注意到将查询器链接在一起时灵活性会降低。也就是说，我觉得 joins(:table1) 比 joins('inner join
ruby-on-rails - Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN
我有这个代码 User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, co
join - Doctrine join 绕过延迟加载
我刚刚开始探索 Symfony2，我很惊讶它拥有如此多的强大功能。我开始做博客教程在: http://tutorial.symblog.co.uk/ 但使用的是 2.1 版而不是 2.0 我的问题是我
SQL JOIN 和不同类型的 JOIN
什么是 SQL JOIN什么是不同的类型？最佳答案插图来自 W3schools : 关于SQL JOIN 和不同类型的 JOIN，我们在Stack Overflow上找到一个类似的问题： http
join - Hive Join 返回零记录
我有两个 Hive 表，我正在尝试加入它们。这些表没有被任何字段聚集或分区。尽管表包含公共(public)键字段的记录，但连接查询始终返回 0 条记录。所有数据类型都是“字符串”数据类型。连接查询很
join - solr join - 返回父子文档
我正在使用 Solr 的(4.0.0-beta)连接功能来查询包含具有父/子关系的文档的索引。连接查询效果很好，但我只能在搜索结果中获得父文档。我相信这是预期的行为。但是，是否有可能在搜索结果中同时
join - 三向关联查询/has_many :through/join
我正在使用可用的指南/api/书籍自学 Rails，但我无法理解通过三种方式/嵌套 has_many :through 关联进行的连接。我有用户与组相关联:通过成员(member)资格。我在多对多
SQL JOIN 和不同类型的 JOIN
什么是 SQL JOIN，有哪些不同的类型？最佳答案插图来自 W3schools : 关于SQL JOIN 和不同类型的 JOIN，我们在Stack Overflow上找到一个类似的问题： htt
Mysql join 使所有 join
我正在尝试访问数据库的两个表。在商店里，我保留了一个事件列表，其中包含 Table Event id, name,datei,houri, dateF,Hourf ,capacity, age ,de
mysql - 复杂连接(Joining Joins)
我有 4 个表:booking、address、search_address 和 search_address_log 表:(相关列) 预订:(pickup_address_id, dropoff_a
Joining after join with yq(在与yq连接之后进行连接)
我在YML中有以下结构：。我正试着创造一个这样的结构：。作业名称和脚本用~分隔，作业用；分隔。。我可以使用以下命令使其正常工作。然而，我想知道是否可以用一个yq表达式来完成，而不是通过管道再次使用yq
Joining after join with yq(在与yq连接之后进行连接)
我在YML中有以下结构：。我正试着创造一个这样的结构：。作业名称和脚本用~分隔，作业用；分隔。。我可以使用以下命令使其正常工作。然而，我想知道是否可以用一个yq表达式来完成，而不是通过管道再次使用yq

首页

博学

6Ren·AI

商城

sql - Oracle left join 表现得像 inner join