在写左关联时SQL语句出现 Duplicate column name 'NAME'名字重复错误解决方法

话不多说直接上代码：

SELECT *

      FROM `personrequireupdate` a LEFT JOIN (select p.*,o.officeid,o.name as officename from person_modifyinf p
      LEFT JOIN office o   on o.officeid=p.officeid)p  ON p.personId=a.personId 
      LEFT JOIN personrole p2 ON a.personId = p2.personid 
      WHERE a.state=0 and p.state=0

这样它就会报错 ：

SELECT *

      FROM `personrequireupdate` a LEFT JOIN (select p.*,o.officeid ,o.name as officename from person_modifyinf p
      LEFT JOIN office o   on o.officeid=p.officeid)p  ON p.personId=a.personId 
      LEFT JOIN personrole p2 ON a.personId = p2.personid 
      WHERE a.state=0 and p.state=0

1060 - Duplicate column name 'officeid'
时间: 0.083s

改成这样子：

SELECT * 
      FROM `personrequireupdate` a LEFT JOIN (select p.*,o.officeid AS offid,o.name as officename from person_modifyinf p
      LEFT JOIN office o   on o.officeid=p.officeid)p  ON p.personId=a.personId 
      LEFT JOIN personrole p2 ON a.personId = p2.personid 
      WHERE a.state=0 and p.state=0

代码运行正常 ：

SELECT *

    
             FROM `personrequireupdate` a LEFT JOIN (select p.*,o.officeid AS offid,o.name as officename from person_modifyinf p
      LEFT JOIN office o   on o.officeid=p.officeid)p  ON p.personId=a.personId 
      LEFT JOIN personrole p2 ON a.personId = p2.personid 
      WHERE a.state=0 and p.state=0

OK
时间: 0.059s
看出为什么了吗？没错，就是因为在我们写关联语句时同样的字段出现了两次，没有给他们起别名引起的，就像我写的这个：o.officeid AS offid 。因此，在进行关联查询时，如果两张或几张表都有那同样的字段，要给它起个别名。