解决 Oracle column ambiguously defined 的问题

最新推荐文章于 2024-08-15 14:34:29 发布

原创最新推荐文章于 2024-08-15 14:34:29 发布 · 9.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文通过两个SQL查询案例，解析了在进行复杂联接操作时遇到的列定义不明确错误，并提供了修正方法，强调了为多列取别名的重要性。

1、案例1：

select * from(
select aa2.workorder,aa2.*,bb.* from
(select distinct t.workorder,t.client,t.product_type,t.batch_qty from table t )aa2
right join
(select t.workorder,count(t.workorder) as qianshuliang from table t
group by t.workorder )bb on (aa2.workorder = bb.workorder)
)a1
right join (select t.workorder,count(t.workorder) as benshuliang from table t group by t.workorder
)b1 on (a1.workorder=b1.workorder)

出现错误

2、案例2：

select * from(
select aa2.workorder,aa2.client,aa2.product_type,bb.qianshuliang from
(select distinct t.workorder,t.client,t.product_type,t.batch_qty from table t )aa2
right join
(select t.workorder,count(t.workorder) as qianshuliang from table t
group by t.workorder )bb on (aa2.workorder = bb.workorder)
)a1
right join (select t.workorder,count(t.workorder) as benshuliang from table t group by t.workorder
)b1 on (a1.workorder=b1.workorder)

3、原因：

在取多个列时，需要给列一个别名，如上文否则出现

Oracle column ambiguously defined（列定义不明确的问题）