mysql - JOIN 语句中双嵌套派生表中的未知列

Question

在我的数据库中，有两种类型的图像:挑战和答案。他们都有纬度/经度位置列。在此查询中，我希望通过 INNER JOIN 选择用户最近创建的挑战或答案。

(这背后的业务逻辑:我们基本上想要获得一个用户列表，其中包括用户最后的已知位置，这由他们最近的挑战或决定答案 - 以最近的为准。如果用户没有最后已知的位置，则他们不应包含在此列表中。)

我收到 [Err] 1054 - 'Where 子句'中的未知列 'U.Id':

SELECT
    U.Id,
    U.TotalPoints,
    LastImage.Lat,
    LastImage.Lng
FROM User U
INNER JOIN
(
    SELECT Lat, Lng FROM
    (
        (SELECT Lat, Lng, CreatedOn FROM AnswerImage WHERE UserId = U.Id ORDER BY Id DESC LIMIT 1)
        UNION ALL
        (SELECT Lat, Lng, CreatedOn FROM ChallengeImage WHERE UserId = U.Id ORDER BY Id DESC LIMIT 1)
    ) LastImages ORDER BY CreatedOn DESC LIMIT 1
) LastImage
WHERE U.Type = 1 AND U.Status = 2
ORDER BY TotalPoints DESC;

我似乎无法从派生的“LastImages”表(或任何合适的术语)中引用用户表(别名 U)。

有人可以帮忙吗？我试过其他方法，但没有一个能满足我的所有要求:

1. 仅当用户有 至少 1 个挑战或 1 个答案(因此是 UNION)时才返回一行
2. 最新的(由 ChallengeImage.CreatedOn 和 AnswerImage.CreatedOn 确定)图像将用于连接

谢谢!

Answer 1

SELECT u.id,
       u.TotalPoints,
       IF(IFNULL(a.CreatedOn, '1900-01-01') > IFNULL(c.CreatedOn, '1900-01-01'), a.Lat, c.Lat) AS Lat,
       IF(IFNULL(a.CreatedOn, '1900-01-01') > IFNULL(c.CreatedOn, '1900-01-01'), a.Lng, c.Lng) AS Lng
FROM User AS u
LEFT JOIN
    (SELECT UserId, Lat, Lng, CreatedOn
     FROM AnswerImage AS a
     JOIN (SELECT UserId, MAX(CreatedOn) AS CreatedOn
           FROM AnswerImage
           GROUP BY UserId) AS amax
     USING (UserId, CreatedOn)) AS a
    ON u.id = a.UserId
LEFT JOIN
    (SELECT UserId, Lat, Lng, CreatedOn
     FROM ChallengeImage AS c
     JOIN (SELECT UserId, MAX(CreatedOn) AS CreatedOn
           FROM ChallengeImage
           GROUP BY UserId) AS cmax
     USING (UserId, CreatedOn)) AS c
    ON u.id = c.UserId
WHERE U.Type = 1 AND U.Status = 2
    AND (a.Lat IS NOT NULL OR c.Lat IS NOT NULL)
ORDER BY TotalPoints DESC;

子查询是获取表中每组最后一行的常用方法之一，请参阅

Retrieving the last record in each group

然后您将它们与 User 表连接起来，这样即使用户在两个表中都没有匹配项，您也会得到结果。