mysql - 多重联接的 on 子句中的未知列

Question

我试图通过将 Customer 表与已连接的 BuyVehicle & Vehicle 表(我已为其指定别名“t”)连接来获取匹配列对。如果我在第一行中分配了别名，p1.SSN 会如何，我假设 p2.SSN 将遵循未知列？

SELECT p1.SSN, p2.SSN FROM Customer AS p1, Customer AS p2 
INNER JOIN (SELECT BVSSN, BVVin, Vin FROM CarPurchase INNER JOIN Car ON BVVin = Vin) 
AS t ON p1.SSN = BVSSN & p2.SSN = BVSSN
WHERE p1.SSN < p2.SSN AND //matching columns comparisons yet to be added;

ERROR 1054 (42S22): Unknown column 'p1.SSN' in 'on clause'

Answer 1

问题在于不同类型连接的混合。 MySQL 无法识别 p1 表，因为它不是“连接”的一部分。以下内容应该可以解决此问题:

SELECT p1.SSN, p2.SSN
FROM Customer AS p1 cross join
     Customer AS p2 INNER JOIN
     (SELECT BVSSN, BVVin, Vin
      FROM CarPurchase INNER JOIN
           Car
           ON BVVin = Vin
    ) AS t
    ON p1.SSN = BVSSN and p2.SSN = BVSSN
WHERE p1.SSN < p2.SSN AND //matching columns comparisons yet to be added;

实际上，您应该将 WHERE 子句移至 ON 子句:

SELECT p1.SSN, p2.SSN
FROM Customer AS p1 join
     Customer AS p2 
     on  p1.SSN < p2.SS INNER JOIN
     (SELECT BVSSN, BVVin, Vin
      FROM CarPurchase INNER JOIN
           Car
           ON BVVin = Vin
    ) AS t
    ON p1.SSN = BVSSN and p2.SSN = BVSSN