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，能不能发现什么规律呢？

 

有空继续讨论。