获取额外的行-使用Left Join调出3个表后 [英] Getting extra rows - After joing the 3 tables using Left Join

问题描述

SELECT (b.descr || ' - ' || c.descr) description
  FROM tbl1 a LEFT JOIN tbl2 b ON a.ACCOUNT = b.ACCOUNT 
       LEFT JOIN tbl3 c ON a.product = c.product
 WHERE a.descr50 = ' ' ;  

table1只有7622行，且descr50 =''，但是此选择返回7649行.您能帮我吗?预先感谢

table1 has only 7622 rows with descr50 = ' ' but this select is returning 7649 rows. Could you please help me in this? thanks in advance

推荐答案

一起使用两个或多个表时，您可以有效地获得这些表的笛卡尔积，并应用在JOIN条件下声明的过滤器

When you JOIN two or more table together, you effectively get a cartesian product for these tables to which a filter stated in the JOIN condition is applied.

当您使用过时的隐式JOIN语法时，这一点更加明显.

This is more obvious when you use an obsolete implicit JOIN syntax.

LEFT JOIN保证您不会比最左边的表所包含的 less 行少. e.最左边表格的每一行至少返回一次.

The LEFT JOIN guarantees that you get no less rows than the leftmost table contains, i. e. each row from the leftmost table is returned at least once.

如果过滤器不是一对一的行映射，您仍然可以获得更多的行.

You can still get more rows, if the filter is not a one-to-one row mapping.

在您的情况下:

SELECT  (b.descr || ' - ' || c.descr) description
FROM    tbl1 a
LEFT JOIN
        tbl2 b
ON      b.ACCOUNT = a.ACCOUNT
LEFT JOIN
        tbl3 c
ON      c.product = a.product
WHERE  a.descr50 = ' '

acccount或product在b或c中不是唯一的.

either acccount or product are not unique in b or c.

对于这些行:

a.account

1
2
3

b.account  b.description

1          Account 1
2          Account 2 - old
2          Account 2 - new

，JOIN将返回以下内容:

a.account b.account b.description

1         1          Account 1
2         2          Account 2 - old
2         2          Account 2 - new
3         NULL       NULL

，为您提供的行数超过了两个表中的任何一个.

, giving you more rows than either of the tables contains.

要仅从任一表中选择第一个匹配的描述，请使用以下方法:

To just pick the first matching description from either table, use this:

SELECT  (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl2 b
        WHERE   b.account = a.account
                AND rownum = 1
        ) || ' - ' ||
        (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl3 c
        WHERE   c.product= a.product
                AND rownum = 1
        ) description
FROM    tbl1 a
WHERE   a.descr50 = ' '

要更新，只需将查询包装到内联视图中即可:

To update, just wrap the query into an inline view:

UPDATE  (
        SELECT  (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl2 b
                WHERE   b.account = a.account
                        AND rownum = 1
                ) || ' - ' ||
                (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl3 c
                WHERE   c.product= a.product
                        AND rownum = 1
                ) description
        FROM    tbl1 a
        WHERE   a.descr50 = ' '
        )
SET     descr50 = description

这篇关于获取额外的行-使用Left Join调出3个表后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！