首页 文章

Oracle留下外连接问题

提问于
浏览
0

我在使用左外连接的报告SQL中遇到问题 .

SELECT * FROM xx.aa 
           LEFT OUTER JOIN xx.yyy
           ON aa.some_column_1 = yyy.some_column_1
           AND aa.some_column_2 = yyy.some_column_2
           AND aa.some_column_3 = yyy.some_column_3
           AND yyy.some_year = '2015'
           AND yyy.some_column_4 IS NOT NULL
           AND yyy.some_column_4 > 0
 WHERE aa.some_column_1 = '1234' 
   AND aa.some_column_2 = 'SOME_VALUE'
   AND aa.end_date >= TO_DATE('01-JAN-2015','DD-MON-YYYY')
   AND aa.start_date <= TO_DATE('31-DEC-2015','DD-MON-YYYY')
   AND (aa.some_column_3 = 'SOME_VALUE')

表aa

some_column_1 some_column_2 some_column_3 start_date end_date
1234          SOME_VALUE    SOME_VALUE    1/1/2011   6/30/2014
1234          SOME_VALUE    SOME_VALUE    7/1/2014   6/30/2015

表yyy

some_column_1 some_column_2 some_column_3 some_column_4 some_year
1234          SOME_VALUE    SOME_VALUE    444           2015

但是,左外连接中的最后两个条件 IS NOT NULL> 0 应仅在超过1行匹配且仅至少其中一行满足两个条件时应用 . 否则这将是一个问题 .

有任何想法吗?

1 回答

  • 1

    您应该在没有左连接条件的情况下运行查询,然后对您的条件进行讨论,以确定yyy表中的值是否为null .

    select aa_col1, aa_col2, ..., aa_coln,
        case when cnt > 2 and cond > 1 
                  and not (yyy_some_column_4 IS NOT NULL AND yyy_some_column_4 > 0) 
             then null else yyy_col1 
             end as yyy_col1,
        ,...the other columns of yyy
    from(
        SELECT aa.*, yyy.*,
          count(*) over() as cnt,
          count(case when yyy.some_column_4 IS NOT NULL AND yyy.some_column_4 > 0  then 1 end) over() as cond
        FROM xx.aa 
        LEFT OUTER JOIN xx.yyy
            ON aa.some_column_1 = yyy.some_column_1
                   AND aa.some_column_2 = yyy.some_column_2
                   AND aa.some_column_3 = yyy.some_column_3
                   AND yyy.some_year = '2015'
         WHERE aa.some_column_1 = '1234' 
           AND aa.some_column_2 = 'SOME_VALUE'
           AND aa.end_date >= TO_DATE('01-JAN-2015','DD-MON-YYYY')
           AND aa.start_date <= TO_DATE('31-DEC-2015','DD-MON-YYYY')
           AND (aa.some_column_3 = 'SOME_VALUE')
    )
    

    在第8行,您应该对列进行别名,以使它们在上部选择中是不同的 . 我把它们命名为aa_col1,yyy_col1等 .

相关问题