此难题，如何解决最优

业务表：层1_ID     层2_ID     层3_ID    金额
--------------------------------------
A           AA         AAA       100.00
A           AA                   200.00
A                                300.00维度表:层1_ID   层1_NAME   层2_ID   层2_NAME   层2_ID   层2_NAME
---------------------------------------------------------
A         N_A        AA        N_AA      AAA      N_AAA
A         N_A        AA        N_AA 
A         N_A要求结果如下：层1_NAME     层2_NAME     层3_NAME    金额
-----------------------------------------------
N_A           N_AA         N_AAA       100.00
N_A           N_AA                     200.00
N_A                                    300.00该SQL该如何写？？

解决方案 »

  1.   

    --试试，没测试
    select a.层1_NAME,a.层2_NAME,a.层3_NAME,b.金额
    from 维度表 a,业务表 b
    where nvl(a.层1_ID,1)=nvl(b.层1_ID,1) 
    and nvl(a.层2_ID,1)=nvl(b.层2_ID,1) 
    and nvl(a.层3_ID,1)=nvl(b.层3_ID,1)
      

  2.   

    select a.层1_NAME,a.层2_NAME,a.层3_NAME,b.金额
    from 维度表 a,业务表 b
    where decode(a.层1_ID,b.层1_ID,1,0)=1
    and decode(a.层21_ID,b.层2_ID,1,0)=1 
    and decode(a.层3_ID,b.层3_ID,1,0)=1
      

  3.   

    对于decode,比较特殊的地方就是当两字段均为null时认为两者相等
      

  4.   

    SQL> select decode(null,null,1,0) from dual;
     
    DECODE(NULL,NULL,1,0)
    ---------------------
                        1
     
    SQL> 
      

  5.   

    呵呵,还是有点差异的.如果id就是1的话,decode还是可以用,nvl(id,1)就会出现问题.不过对于楼主的情况,这两者应该是没差异的.
      

  6.   

    "TXRAE84LLQN6E7BR2K5D9NH9LEHJ6N9SPA4ORELE3KZK8OPG6YHRYQBCRJYJ2MYO2M5X5NE4XMF5IO344Z7D7CS7HXSPM"
      

  7.   

    "TXRAE84LLQN6E7BR2K5D9NH9LEHJ6N9SPA4ORELE3KZK8OPG6YHRYQBCRJYJ2MYO2M5X5NE4XMF5IO344Z7D7CS7HXSPM"