SQL错误总结

ORA-00918: column ambiguously defined

异常原因：

select 查询的字段在from的两张表中都存在，导致数据库无法区别需要查询的字段来自于哪张表

以下是例子

select  distinct sales_branch_no, sales_code   
from (select sales_branch_no,sales_code FROM appl_state a,insur_appl b WHERE
 a.appl_id =b.appl_id
AND b.new_appl_stat not in ('V', 'M', 'B', 'R', 'W','F', 'Q', 'D')
AND b.sales_channel='PA') t1
inner join       
(select sales_branch_no,sales_code
FROM svr_group
WHERE  in_force_date is null AND sales_channel='PA' AND sg_type='M') t2
on t1.sales_branch_no= t2.sales_branch_no  and t1.sales_code = t2.sales_code
MR.MACHINE_ID = MM.MACHINE_ID

就是因为t1和t2这两张表中都有sales_branch_no,sales_code这些字段，导致了列定义不明确，数据库无法确定需要查询出来的sales_branch_no数据来自于哪张表。

改为

select  distinct t1.sales_branch_no, t1.sales_code    
from (select sales_branch_no,sales_code FROM appl_state a,insur_appl b WHERE
a.appl_id =b.appl_id
AND b.new_appl_stat not in ('V', 'M', 'B', 'R', 'W','F', 'Q', 'D')
AND b.sales_channel='PA') t1
inner join       
(select sales_branch_no,sales_code
FROM svr_group
WHERE  in_force_date is null AND sales_channel='PA' AND sg_type='M') t2
on t1.sales_branch_no= t2.sales_branch_no  and t1.sales_code = t2.sales_code
MR.MACHINE_ID = MM.MACHINE_ID