mysql - SQL (MySQL) - 如何在内连接中使用嵌套查询中指定的名称

Question

SELECT name, age
FROM (SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
      ) AS C
INNER JOIN (SELECT (Max(sales), person
            FROM C) AS D ON .....

我正在尝试编写一个与上面类似的查询，但是当我在内连接部分引用 C 时，它说它不存在。我如何引用它？

Answer 1

您不能这样做，只能在子查询中使用子查询。

SELECT name, age
FROM 
(
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
) AS C
INNER JOIN
(
    SELECT Max(sales), person
    FROM
    (
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
    ) AS C
) AS D ON ....

如果你的mysql版本支持CTE

WITH C AS ( 
      SELECT name, age / 2
      FROM maintable
      INNER JOIN ......
    )
SELECT name, age
FROM C JOIN 
(
    SELECT Max(sales), person
    FROM C
) AS D ON ....