mysql - 即使使用 Alias，MySQL 中的列也不明确

Question

我有以下 MySQL:

select  `car`.`ID` AS `ID`,
    `title`,`text`
    from `car` 
    LEFT JOIN `truck` as bigcar ON bigcar.`ID` = `car`.`truckID` 
    WHERE `ID` ='1';

出于某种原因，我得到了

Column 'ID' in where clause is ambiguous

由于复杂的代码原因，我不会将别名重命名为其他名称(例如 ID2)
这行不通吗？

Answer 1

您需要在 where 子句中指定用于 id 的表。否则查询将不知道使用哪一个，因为两个表中的列名相同

SELECT
 `car`.`ID` AS `ID`, `title`,`text`
FROM `car` 
LEFT JOIN `truck` as bigcar ON bigcar.`ID` = `car`.`truckID` 
WHERE `car`.`ID` ='1';

或者如果您想要 bigcar ID，您将必须使用:

WHERE `bigcar`.`ID` ='1'; // not truck but bigcar which is the alias you created