在写左关联时SQL语句出现 Duplicate column name 'NAME'名字重复错误解决方法 |
您所在的位置：网站首页 › sql一对多join › 在写左关联时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 。因此，在进行关联查询时，如果两张或几张表都有那同样的字段，要给它起个别名。 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |