代码之家 › 专栏 › 技术社区 › Christopher Smith

将subselect转换为联接

db2-400 sql

Christopher Smith · 技术社区 · 7 年前

我似乎理解Join比sub select更受欢迎。我看不出如何将3个子选择变成连接。

我的sub选择仅获取第一行

如果这不是一个冒犯性的SQL,我完全愿意对此置之不理。

这是我的查询,是的,这些确实是表名和列名

select x1.*, x2.KTNR, x3.J6NQ
from 
    (select D0HONB as HONB, D0HHNB as HHNB, 
        (
            select DHHHNB 
            from ECDHREP 
            where DHAOEQ = D0ATEQ and DHJRCD = D0KNCD 
            order by DHEJDT desc 
            FETCH FIRST 1 ROW ONLY
        ) as STC_HHNB,
        (
            select FIQ9NB 
            from DCFIREP 
            where FIQ7NB = D0Q7NB 
              AND FIBAEQ = D0ATEQ 
              and FISQCD = D0KNCD 
              and FIGZSZ in ('POS', 'ACT', 'MAN', 'HLD') 
            order by FIYCNB desc 
            FETCH FIRST 1 ROW ONLY
        ) as BL_Q9NB,
        (
            select AAKPNR 
            from C1AACPP 
            where AACEEQ = D0ATEQ and AARCCE = D0KNCD and AARDCE = D0KOCD 
            order by AAHMDT desc, AANENO desc 
            FETCH FIRST 1 ROW ONLY 
        ) as NULL_KPNR  
        from ECD0REP
    ) as x1 
left outer join (
        select AAKPNR as null_kpnr, max(ABKTNR) as KTNR 
        from C1AACPP 
          left outer join C1ABCPP on AAKPNR = ABKPNR 
        group by AAKPNR
    ) as X2 on x1.NULL_KPNR = x2.null_KPNR 
left outer join (
        select ACKPNR as KPNR, count(*) as J6NQ 
        from C1ACCPP 
        WHERE ACJNDD = 'Y' 
        group by ACKPNR
    ) as X3 on x1.NULL_KPNR = x3.KPNR

2 回复 | 直到 7 年前

Charles 7 年前

您已经得到了相关子选择和嵌套表表达式(NTE)的组合。

就我个人而言,如果我必须保持它,我会称之为冒犯

考虑常见的表表达式(&P);联接。。。如果没有数据和tabvle结构,我无法给出真正的语句,但一般形式如下

with 
   STC_HHNB as (
     select DHHHNB, DHAOEQ, DHJRCD, DHEJDT
      from ECDHREP )
,  BL_Q9NB as ( <....>
               where FIGZSZ in ('POS', 'ACT', 'MAN', 'HLD'))
<...>
select <...>
from stc_hhb
     join blq9nb on <...>

CTE优于NTE的两个重要原因。。。CTE的结果可以重用,也可以轻松地用CTE增量构建语句。

我的意思是你可以

with 
  cte1 as (<...>)
  , cte2 as (select <...> from cte1 join <...>)
  , cte3 as (select <...> from cte1 join <...>)
  , cte4 as (select <...> from cte2 join cte3 on <...>)
select * from cte4;

优化器可以选择为cte1构建临时结果集并多次使用。从建筑的角度来看,你可以看到我在建造前面的每个cte。

这是一篇好文章 https://www.mcpressonline.com/programming/sql/simplify-sql-qwithq-common-table-expressions

编辑

让我们深入研究您的第一个相关子查询。

select D0HONB as HONB, D0HHNB as HHNB, 
      (
          select DHHHNB 
          from ECDHREP 
          where DHAOEQ = D0ATEQ and DHJRCD = D0KNCD 
          order by DHEJDT desc 
          FETCH FIRST 1 ROW ONLY
        ) as STC_HHNB
from ECD0REP

您要求DB做的是,对于ECD0REP中读取的每一行,从ECDHREP中获取一行。如果您运气不好,DB将不得不读取ECDHREP中的大量记录才能找到那一行。通常,考虑使用相关子查询时,内部查询需要读取 每一个 一行所以如果外部有M行,内部有N行。。。然后您将看到正在读取的MxN行。

我以前见过这种情况,尤其是在IBM I上。因为RPG开发人员会这样做

read ECD0REP;
dow not %eof(ECD0REP);
  //need to get DHHHNB from ECDHREP 
  chain (D0ATEQ, D0KNCD) ECDHREP;
  STC_HHNB = DHHHNB;
  read ECD0REP;
enddo;

但在SQL中,这不是实现它的方法。SQL应该是基于集合的。

因此,您需要做的是考虑如何从ECDHREP中选择与您想要从ECD0REP中获得的记录集相匹配的记录集。

with cte1 as (
  select DHHHNB, DHAOEQ, DHJRCD
  from ECDHREP
)
select D0HONB as HONB
     , D0HHNB as HHNB
     , DHHHBN as STC_HHNB
from ECD0REP join cte1 
      on DHAOEQ = D0ATEQ and DHJRCD = D0KNCD

现在也许这不太正确。也许ECDHREP中有多行具有相同的值(DHAOEQ、DHJRCD);因此,您需要 FETCH FIRST 在相关子查询中。很好,你可以专注于CTE,找出需要做什么才能得到你想要的那一行。可能 MAX(DHHHNB) 或 MIN(DHHHNB) 会有用的。如果没有别的,你可以 ROW_NUMBER() 只选一行。。。

with cte1 as (
  select DHHHNB, DHAOEQ, DHJRCD
         , row_number() over(partition by DHAOEQ, DHJRCD
                             order by DHAOEQ, DHJRCD)
            as rowNbr  
  from ECDHREP
), cte2 as (
   select DHHHNB, DHAOEQ, DHJRCD
     from cte1
    where rowNbr = 1
)
select D0HONB as HONB
     , D0HHNB as HHNB
     , DHHHBN as STC_HHNB
from ECD0REP join cte2
      on DHAOEQ = D0ATEQ and DHJRCD = D0KNCD

现在,您正在处理多组记录,将它们连接在一起以获得最终结果。

更糟糕的情况是,DB必须读取M+N条记录。

这不是关于表演,而是关于成套思维。

当然,对于使用相关子查询的简单语句,优化器可能能够将其重新写入联接。

但最好是尽可能编写最好的代码,而不是希望优化器能够纠正它。

我看到并重写了100个相关的查询;常规子查询。。。。事实上,我看到一个查询必须分成两部分,因为有两个多个子查询。DB对每条语句的限制为256。

jmarkmurphy 7 年前

如果 FETCH FIRST 1 ROW ONLY 条款是必要的。在这种情况下,您可能无法将这些子选项拉到CTE中,因为该CTE中只有一行。我怀疑您可以将外部sub-select拉入CTE,但您仍然需要CTE中的sub-select。既然似乎没有分享,我就称之为个人偏好。顺便说一句,出于同样的原因,我认为将子选择拉入联接也不会对您起作用。

sub select和CTE之间有什么区别?

with mycte as (
  select field1, field2 
  from mytable
  where somecondition = true)
select * 
from mycte

vs。

select * 
from (select field1, field2 
      from mytable
      where somecondition = true) a

这实际上只是个人喜好,尽管根据具体需求,CTE可以在SQL语句中多次使用,但在其他情况下,如 FETCT FIRST 你问题中的从句。

编辑
让我们看一下第一个子查询。使用适当的索引:

(
    select DHHHNB 
    from ECDHREP 
    where DHAOEQ = D0ATEQ and DHJRCD = D0KNCD 
    order by DHEJDT desc 
    FETCH FIRST 1 ROW ONLY
) as STC_HHNB,

输出集中每行只需读取一条记录。我认为这并不十分繁重。第三个相关子查询也是如此。

第一个相关子查询上的索引为:

create index ECDHREP_X1 
  on ECDHREP (DHAOEQ, DHJRCD, DHEJDT);

第二个相关子查询可能需要每行多读一次,这只是因为 IN 谓词,但它远不需要完整的表扫描。