具有外部左联接的SQL子查询

Question

我需要从表A中选择所有行，如果表B行与表B选择位置子句匹配，则从表B中选择匹配的行。这是我的外部左连接与子查询，但它失败了。有人可以帮忙吗？

SELECT A.WRDWHSE,
       A.WRDRTID,
       A.WRDRPTLIN
FROM CWDDEV.WRTEMSTDP AS A 
LEFT JOIN (SELECT B.RHRTID,
                  B.RHRTE, 
                  B.RHSTAT, 
                  B.RHPRTT 
           FROM PIRF3.RTEHED
           WHERE B.RHWHSE=003 and 
                 ((B.RHSTSD =20151111 and B.RHSTST >=060000 ) or (B.RHSTSD=20151112 and B.RHSTST <=055959 )) and
                 B.RHTYPE NOT IN ('W') and 
                 (B.RHRTE NOT LIKE 'EMP%') and
                 B.RHSTAT IN (1, 3, 4, 5, 6)) AS B                                                     
ON A.WRDWHSE=B.RHWHSE and A.WRDRTID=B.RHRTE
WHERE A.WRDWHSE=003     
ORDER BY A.WRDRPTLIN               

Answer 1

以下是我最初的想法，与你的问题一致：

SELECT *
FROM
    CWDDEV.WRTEMSTDP A LEFT OUTER JOIN
    (
    SELECT
        B.RHWHSE, B.RHRTE,
        B.RHRTID, B.RHSTAT, B.RHPRTT /* are these going to be used? */
    FROM PIRF3.RTEHED B
    WHERE
            B.RHWHSE = 003 /* why all the leading zeros? are these numeric or a char? */
        AND (
               /* if these are really dates then these won't work */
               (B.RHSTSD = 20151111 AND B.RHSTST >= 060000)
            OR (B.RHSTSD = 20151112 AND B.RHSTST <= 055959)
        )
        AND B.RHTYPE NOT IN ('W')
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    ) B_outer /* inside and outside have different names */
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE A.WRDWHSE = 003
ORDER BY A.WRDRPTLIN      

编辑：根据下面的对话，我愿意考虑一个DB2 / AS400错误。使用左联接的方案非常常见。但以下是查询的一些变体，这些变体可能会起作用或有助于找出一些东西。

消除冗余条件：

SELECT *
FROM
    CWDDEV.WRTEMSTDP A LEFT OUTER JOIN
    (
    SELECT B.RHWHSE, B.RHRTE, B.RHRTID, B.RHSTAT, B.RHPRTT
    FROM PIRF3.RTEHED B
    WHERE
        (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    ) B_outer
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73
ORDER BY A.WRDRPTLIN

公用表表达式 （CTE）：

WITH ROUTES AS ( /* this might not be valid if your platform is old */
    SELECT
        B.RHWHSE, B.RHRTE, B.RHRTID, B.RHSTAT, B.RHPRTT
    FROM PIRF3.RTEHED B
    WHERE
        (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
)
SELECT *
FROM CWDDEV.WRTEMSTDP A LEFT OUTER JOIN ROUTES B
    ON A.WRDWHSE = B.RHWHSE AND A.WRDRTID = B.RHRTE
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73
ORDER BY A.WRDRPTLIN

联盟：

SELECT
    A.WRDWHSE, A.WRDRTID, A.WRDRPTLIN,
    B.RHRTE, B.RHSTAT, B.RHPRTT
FROM
    CWDDEV.WRTEMSTDP A INNER JOIN PIRF3.RTEHED B
        ON A.WRDWHSE = B_outer.RHWHSE AND A.WRDRTID = B_outer.RHRTE
WHERE
        A.WRDWHSE = 3 AND A.WRDRTID = 73
    (
           B.RHSTSD = 20151111 AND B.RHSTST >= 060000
        OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
    )
    AND B.RHTYPE <> 'W'
    AND B.RHRTE NOT LIKE 'EMP%'
    AND B.RHSTAT IN (1, 3, 4, 5, 6)
UNION ALL
SELECT
    A.WRDWHSE, A.WRDRTID, A.WRDRPTLIN,
    NULL AS RHRTE, NULL AS RHSTAT, NULL AS RHPRTT
FROM CWDDEV.WRTEMSTDP A
WHERE A.WRDWHSE = 3 AND A.WRDRTID = 73 AND NOT EXISTS (
    SELECT 1
    FROM PIRF3.RTEHED B
    WHERE
            A.WRDWHSE = B.RHWHSE AND A.WRDRTID = B.RHRTE
        AND (
               B.RHSTSD = 20151111 AND B.RHSTST >= 060000
            OR B.RHSTSD = 20151112 AND B.RHSTST <= 055959
        )
        AND B.RHTYPE <> 'W'
        AND B.RHRTE NOT LIKE 'EMP%'
        AND B.RHSTAT IN (1, 3, 4, 5, 6)
    )
ORDER BY A.WRDRPTLIN

如果您真的需要使其工作，您也可以尝试视图或临时表。听起来您提到的嵌套循环可能是您最好的解决方法。