完美解决Column ‘xxx‘ in field list is ambiguous问题

复现问题

• 使用如下sql查询数据：

•  SELECT  id as id,    `user`.login_name AS user_mobile,    apply_status,    ( SELECT `value` FROM data_dict WHERE CODE = apply_status ) AS apply_status_value,    apply_no,    application_name,    belong_org_code,    belong_org_data_dict.`value` AS belong_org_code_value,    business_contact_name,    business_contact_mobile,    auth_way,    ( SELECT `value` FROM data_dict WHERE CODE = auth_way ) AS auth_way_value,    REPLACE ( REPLACE ( apply_service_type, '"', '' ), '"', '"' ) apply_service_type,    apply_need_desc,    apply_time,    audit_time,    audit_suggest FROM    application_apply LEFT JOIN user ON user.id = application_apply.apply_user_idLEFT JOIN data_dict belong_org_data_dict ON belong_org_data_dict.`code` = application_apply.belong_org_code 

  ​

• 却报出如下错误：

• 

  即Column 'id' in field list is ambiguous

分析问题

• 我们在解决问题之前，首先要分析问题。做到知其然，知其所以然，这样才能有所成长，进而避坑。

• 将Column 'id' in field list is ambiguous翻译成中文就是字段列表中的列id不明确。

• 为什么不明确这个id呢？

• 通过如上的·Mysql语句可得，application_apply表关联user表，但 application_apply表中存在id字段，而user表中也存在id字段。但如上mysql语句，并没有说明id字段是哪张表中的，因而mysql认为这个id字段是不明确的。

解决问题

• 既然知道问题的原因，我们便可如下修改SQL语句

•  SELECT    application_apply.id as id,    `user`.login_name AS user_mobile,    apply_status,    ( SELECT `value` FROM data_dict WHERE CODE = apply_status ) AS apply_status_value,    apply_no,    application_name,    belong_org_code,    belong_org_data_dict.`value` AS belong_org_code_value,    business_contact_name,    business_contact_mobile,    auth_way,    ( SELECT `value` FROM data_dict WHERE CODE = auth_way ) AS auth_way_value,    REPLACE ( REPLACE ( apply_service_type, '"', '' ), '"', '"' ) apply_service_type,    apply_need_desc,    apply_time,    audit_time,    audit_suggest FROM    application_apply LEFT JOIN user ON user.id = application_apply.apply_user_idLEFT JOIN data_dict belong_org_data_dict ON belong_org_data_dict.`code` = application_apply.belong_org_code 

  ​

• 即在id前加上application_apply.查询结果如下图所示：

• 

   

来源地址：https://blog.csdn.net/Andrew_Chenwq/article/details/129253748