mysql - 合并 2 个表以产生 1 个输出 - SQL

Question

我在下面有一个查询，它有效。

$query_for_cat3 = "SELECT   sum,candidate_no,@curRank := @curRank + 1 AS rank
FROM        (SELECT      SUM(score) / 5 SUM,candidate_no
          FROM        SCORE 
          WHERE category_no='$category_no1'
          GROUP BY    candidate_no
        ) a, ( SELECT @curRank := 0 ) r

ORDER BY    sum DESC,candidate_no DESC
LIMIT 5";

我需要做的是将这个查询合并到另一个名为 candidates 的表中。它有列 candidate_no 和 candidate_name。我想根据相应的 candidate_no 生成 candidate_name。

请帮忙。谢谢。

Answer 1

这是您要找的东西吗？

SELECT temp.candidate_no,
       C.candidatename,
       temp.[sum],
       temp.rank
FROM candidate C INNER JOIN
(SELECT   sum,candidate_no,@curRank := @curRank + 1 AS rank
FROM        (SELECT      SUM(score) / 5 SUM,candidate_no
          FROM        SCORE 
          WHERE category_no='$category_no1'
          GROUP BY    candidate_no
        ) a, ( SELECT @curRank := 0 ) r

ORDER BY    sum DESC,candidate_no DESC
LIMIT 5) As temp

ON C.candidate_no=temp.candidate_no