MySQL子查询WHERE IN，还可以从IN子查询中获取额外的列 - MySQL SubQuery WHERE IN, but also get additional column from IN subquery

MySQL SubQuery WHERE IN, but also get additional column from IN subquery

这件事现在让我忘记了，所以假设我有这样的代码：

SELECT * FROM Table1
    WHERE (FoundCity, FoundState) IN
    ( SELECT city, state, distance
      FROM Table2
    ) ;

如何将上面的距离列拉入"main"查询？

将子查询更改为联接。然后，您可以从任一表中选择任意列。

SELECT *,  t2.distance
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.FoundCity = t2.city AND t1.FoundState = t2.state

编辑

如果你想保留Tuple连接语法，你也可以这样做：

SELECT t1.*, t2.distance
    FROM t1 INNER JOIN t2
    ON (t1.FoundCity, t1.FoundState) = (t2.city, t2.state);

SqlFiddle

SELECT * FROM Table1 as T1
LEFT JOIN Table2 as T2 on T1.FoundCity = T2.FoundCity AND T1.FoundState = T2.FoundState

更好的方法是使用Left联接。在*中，您可以使用T1作为表1列的前缀，使用T2作为表2列的前缀来包含所需的列名。（例如：T2状态）