复现问题

  • 使用如下SQL查询数据:

  •  SELECTid 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 FROMapplication_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语句

  •  SELECTapplication_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 FROMapplication_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.查询结果如下图所示: