右/左外部从（）和许多“和”加入JOOQ中的四个表

Question

我是JOOQ的noob，需要帮助将SQL翻译成JOOQ。我这里有一个很长的SQL语句：

请看这张图片以更好地了解：外部连接

SELECT SUM(DECODE(VVS.VVS_ZWS_ID, 47, DECK.DEC_BRUTTOPRAEMIE_100,               DECK.DEC_BRUTTOPRAEMIE_FOLGE))
            FROM deck, agd, vvs, agv
            WHERE vvs.vvs_ver_nummer = verNummer
            AND vvs.vvs_nummer = vvsNummer 
            AND agv.agv_code = vvs.vvs_agv_code
            AND deck.dec_ver_nummer = vvs.vvs_ver_nummer
            AND deck.dec_vvs_nummer = vvs.vvs_nummer
            AND deck.dec_tbl_code = 'KH'
            AND agd.agd_code(+) = deck.dec_agd_code
            AND (    NVL(agv.agv_aenderungstyp, 'NULL') IN ('4', '5')
            OR NVL(agd.agd_aenderungstyp, 'NULL') NOT IN ('4', '5');

我将SQL声明翻译如下：

create.select(sum(decode().when(vvs.VVS_ZWS_ID.eq(47),deck.DEC_BRUTTOPRAEMIE_100)
                                    .otherwise(deck.DEC_BRUTTOPRAEMIE_FOLGE)))
            .from(deck, vvs, agv)
            .rightOuterJoin(agd)
            .on(agd.AGD_CODE.eq(deck.DEC_AGD_CODE))
            .where(vvs.VVS_VER_NUMMER.eq(verNummer))
            .and(vvs.VVS_NUMMER.eq(vvsNummer))
            .and(agv.AGV_CODE.eq(vvs.VVS_AGV_CODE))
            .and(deck.DEC_VER_NUMMER.eq(vvs.VVS_VER_NUMMER))
            .and(deck.DEC_VVS_NUMMER.eq(vvs.VVS_NUMMER))
            .and(deck.DEC_TBL_CODE.eq("KH"))
            .and(nvl(agv.AGV_AENDERUNGSTYP, "NULL")
              .in("4", "5")
              .or(nvl(agd.AGD_AENDERUNGSTYP, "NULL")
              .notIn("4", "5")))
            .fetch();

我的问题是：1<代码>agd表格必须与agd上的甲板表格连接。agd_代码（）=甲板。dec_agd_代码。我是否正确翻译了sql
2.vvs和agv应该如何处理，因为这些表位于from（）中agd的后面？

我花了两天时间思考这个问题，但我解决不了这个问题。

谢谢

Answer 1

虽然我通常不建议使用已弃用的Oracle风格的外连接语法，但请注意，jOOQ确实通过Field.plus（）支持它，因此您可以编写：

SQL

AND agd.agd_code(+) = deck.dec_agd_code

JOOQ

.and(agd.AGD_CODE.plus().eq(deck.DEC_AGD_CODE))

除此之外，这个问题更容易解释。您将“经典”表列表（来自子句中的几个表）与ANSI连接混合在一起，这总是令人困惑。使用jOOQ时，jOOQ会将ANSI连接表达式附加到表列表的最后一个表中。在您的情况下，这导致了以下FROM子句：

-- Parentheses added for illustration purposes
FROM deck, vvs, (agv RIGHT OUTER JOIN agd ON agd.AGD_CODE = deck.DEC_AGD_CODE)

在这种情况下，您可能需要的是LEFT OUTER JOIN，以防您的查询可能变得正确。

但这仍然会让人困惑，因此在任何情况下，我强烈建议您首先将查询迁移到所有ANSI连接：

SQL

SELECT SUM(DECODE(VVS.VVS_ZWS_ID, 
              47, DECK.DEC_BRUTTOPRAEMIE_100, 
              DECK.DEC_BRUTTOPRAEMIE_FOLGE))
FROM deck
JOIN vvs
  ON deck.dec_ver_nummer = vvs.vvs_ver_nummer
  AND deck.dec_vvs_nummer = vvs.vvs_nummer
JOIN agv
  ON agv.agv_code = vvs.vvs_agv_code
LEFT JOIN agd
  ON deck.dec_agd_code = agd.agd_code
WHERE vvs.vvs_ver_nummer = verNummer
  AND vvs.vvs_nummer = vvsNummer 
  AND deck.dec_tbl_code = 'KH'
  AND (    NVL(agv.agv_aenderungstyp, 'NULL') IN ('4', '5')
    OR NVL(agd.agd_aenderungstyp, 'NULL') NOT IN ('4', '5'));

JOOQ

create.select(sum(decode().when(vvs.VVS_ZWS_ID.eq(47),deck.DEC_BRUTTOPRAEMIE_100)
                                    .otherwise(deck.DEC_BRUTTOPRAEMIE_FOLGE)))
      .from(deck)
      .join(vvs)
        .on(deck.DEC_VER_NUMMER.eq(vvs.VVS_VER_NUMMER))
        .and(deck.DEC_VVS_NUMMER.eq(vvs.VVS_NUMMER))
      .join(agv)
        .on(agv.AGV_CODE.eq(vvs.VVS_AGV_CODE))
      .leftOuterJoin(agd)
        .on(agd.AGD_CODE.eq(deck.DEC_AGD_CODE))
      .where(vvs.VVS_VER_NUMMER.eq(verNummer))
      .and(vvs.VVS_NUMMER.eq(vvsNummer))
      .and(deck.DEC_TBL_CODE.eq("KH"))
      .and(nvl(agv.AGV_AENDERUNGSTYP, "NULL")
        .in("4", "5")
        .or(nvl(agd.AGD_AENDERUNGSTYP, "NULL")
        .notIn("4", "5")))
      .fetch();