MySQL - 组合多个查询

Question

我目前有以下查询(以及一些相关的 vB 代码)。我希望我可以将其精简为单个 SELECT 语句，而不是必须在每个页面上运行 10 个以上才能获取个人姓名。

$results = $db->query_read_slave("
    SELECT user.id AS steam, user.skills AS level
    FROM wcsp.wcgousers AS user
    WHERE race = '_wcs_' 
    ORDER BY ABS(user.skills) DESC LIMIT 10
    ");

$rank = 1;
while ($user = $db->fetch_array($results)) {
    $result = $db->query_first("
        SELECT old.name AS name
        FROM wcsp.warn_oldnames AS old
        WHERE id = '$user[steam]'
        ORDER BY lasttime
        DESC LIMIT 1
    ");

    $listing[] = array("id" => $user['steam'], "level" => $user['level'], "name" => $result['name'], "rank" => $rank);

    $rank += 1;
}

我已经尝试过 LEFT JOIN 但我遇到的问题是我需要在 LEFT JOIN 中使用类似于以下的子查询:

SELECT user.id AS steam, user.skills AS level, names.name AS name
FROM wcsp.wcgousers AS users
LEFT JOIN 
    (
        SELECT names.name
        FROM wcsp.warn_oldnames AS names
        WHERE id = wcsp.wcgousers.id
        ORDER BY lasttime DESC LIMIT 1
    ) AS names
ON names.id = users.id
WHERE users.race = '_wcs_'

由于子查询中的不同数据库检查，这将不起作用。

Answer 1

如果我没理解错的话，你想为每个用户获取最新的 Name。

SELECT  a.id AS steam, 
        a.skills AS level,
        b.name
FROM    wcgousers a
        INNER JOIN warn_oldnames b
            ON a.ID = b.ID
        INNER JOIN
        (
            SELECT  ID, MAX(lasttime) max_date
            FROM    warn_oldnames
            GROUP   BY ID
        ) c ON  b.ID = c.ID AND
                b.lastTime = c.max_date
WHERE   a.race = '_wcs_'
-- ORDER  BY ABS(a.skills) DESC 
-- LIMIT  10