MySQL 在计算列的别名上使用内部联接

Question

我有这样的查询:

SELECT User.id, 10*10 as distance
FROM USERS
INNER JOIN
(
    SELECT Location.user_id,
    min(10 * 10) as mindistance
    FROM Location
    GROUP BY Location.user_id
 ) L ON Users.id = Location.user_id AND distance = L.mindistance

如果我保持原样，我会不断得到:

Unknown column 'distance' in 'on clause'

但是如果我把 User.distance 而不是距离，我会得到:

MySQL syntax error near....

我不能在计算字段上以这种方式使用别名吗？ 10 * 10 只是一个简单的占位符，因为计算要复杂得多。

Answer 1

您正试图在 ON 子句中使用列别名。 MySQL documentation对于这种情况有这样的说法:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause.

And also :

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

也就是说，您可以使用变量来计算 ON 子句中的表达式，然后使用列列表中的值，如下所示:

SELECT User.id, @dist as distance
FROM USERS
INNER JOIN
(
    SELECT Location.user_id,
    min(10 * 10) as mindistance
    FROM Location
    GROUP BY Location.user_id
 ) L ON Users.id = Location.user_id AND (@dist := 10*10) = L.mindistance