ORA-00918: column ambiguously defined錯誤應對方法

最近做一個項目，經常需要用到多表查詢，通過需要分頁等，於是經常會碰到

 

ORA-00918: column ambiguously defined錯誤應對方法，意思是說，某一列定義模糊，於是

 

經過了很多方面的測試，總算能解決，尤其是在涉及到tablename.*，這裡的*很容易出現這樣的錯誤。

 

需要仔細的研究下面的2個sql語句：

 

select v1.*,(v1.data-v2.data) as rate, v3.*, (v3.data-v4.data) as rate2

from (

     select rownum as rn, a.* from (

          select ayearmon, DATA, DATA-100 as rate

          from test

          where isvalid=1 and code='3503' 

                         and region = '000000'

          order by ayearmon desc

     ) a) v1,

   (

   select (rownum-1) as rn1, b.* from (

           select ayearmon, DATA

           from test

           where isvalid=1 and code='3503' 

                         and region = '000000'

           order by ayearmon desc                       

     ) b) v2, 

    ( select rownum as rn, c.* from (

          select ayearmon, DATA, DATA-100 as rate

          from test

          where isvalid=1 and code='3502' 

                         and region = '000000'

          order by ayearmon desc

     ) c) v3,

   (

   select (rownum-1) as rn1, d.* from (

           select ayearmon, DATA

           from test

           where isvalid=1 and code='3502' 

                         and region = '000000'

           order by ayearmon desc                       

     ) d) v4 

where v1.rn = v2.rn1 and v3.rn = v4.rn1 and v1.rn = v3.rn

and v1.rn >= 1 and v1.rn <= 10

 

和

 

 

select * from (

  select v1.*, CASE WHEN substr(v1.ayearmon, 5, 2) = '02' THEN v1.DATA ELSE (v1.DATA - v2.DATA)  END  as dy

        from (

             select rownum as rn, a.* from (

                  select ayearmon, DATA

                  from v_yy_MAC201 

                  where isvalid=1 and code='10011001' and length(ayearmon) = 6

                                and timetype='b0301' and region = '000000'

                  order by ayearmon desc

             ) a) v1,

           (

           select (rownum-1) as rn1, b.* from (

                   select ayearmon, DATA

                   from v_yy_MAC201

                   where isvalid=1 and code='10011001' and length(ayearmon) = 6

                                and timetype='b0301' and region = '000000'

                   order by ayearmon desc                       

             ) b) v2 where v1.rn = v2.rn1

)          

where rn >= 1 and rn <= 50

 

 

通過上面的2個sql，能不能發現什麼規律呢？

 

有空繼續討論。