如何将SQL COUNT(*)查询与INNER JOIN查询合并生成排行榜

一、合并查询的正确写法 #

你碰到的Subquery returns more than 1 row错误，大概率是合并查询时的写法问题——直接把排名子查询塞到SELECT列表里，没做好关联导致返回多行。正确的做法是把排名计算做成子查询，再和学生、分数表做JOIN，一次性拿到所有数据：

SELECT 
    r.rank,
    s.username,
    CONCAT(sp.points, 'pts') AS pointspts,
    CONCAT(s.firstname, ' ', s.lastname) AS name,
    CASE WHEN s.sex = 'M' THEN 'Male' ELSE 'Female' END AS sexes,
    s.house,
    CONCAT(s.age, 'yrs') AS ageyrs
FROM students s
INNER JOIN spoints sp ON s.username = sp.username
INNER JOIN (
    SELECT 
        username,
        1 + (SELECT COUNT(*) FROM spoints a WHERE a.points > b.points) AS rank
    FROM spoints b
) r ON s.username = r.username
ORDER BY r.rank ASC;

这个语句的逻辑很直接：

• 先通过子查询r算出每个用户的排名
• 用username把学生表、分数表、排名子查询关联起来
• 最后按排名升序排列，直接得到完整的排行榜数据

二、解决双重循环的重复问题 #

如果因为环境限制必须分开查询再用PHP处理，别用双重循环——那会让每个学生匹配所有排名，必然重复。正确的姿势是先把排名存成以username为键的关联数组，再遍历学生数据时直接取对应排名：

// 先把排名数据存进关联数组，key是username，value是rank
$rankResult = mysqli_query($conn, "SELECT username, 1 + ( SELECT count(*) FROM spoints a WHERE a.points > b.points ) AS rank FROM spoints b ORDER BY rank;");
$rankMap = [];
while ($row = mysqli_fetch_assoc($rankResult)) {
    $rankMap[$row['username']] = $row['rank'];
}

// 再获取学生信息
$query = mysqli_query($conn, "SELECT students.username, 
            CONCAT(spoints.points, 'pts') AS pointspts, 
            CONCAT(students.firstname, ' ', students.lastname) AS name, 
            CASE WHEN sex = 'M' THEN 'Male' ELSE 'Female' END AS sexes, 
            students.house, 
            CONCAT(students.age, 'yrs') AS ageyrs 
        FROM students 
            INNER JOIN spoints ON students.username=spoints.username 
        ORDER BY points DESC");

// 遍历生成结果，每个学生对应唯一排名
while ($student = mysqli_fetch_assoc($query)) {
    $student['rank'] = $rankMap[$student['username']];
    // 这里输出表格行，示例：
    echo "<tr>
            <td>{$student['rank']}</td>
            <td>{$student['name']}</td>
            <td>{$student['pointspts']}</td>
            <td>{$student['sexes']}</td>
            <td>{$student['house']}</td>
            <td>{$student['ageyrs']}</td>
          </tr>";
}

额外调整：相同分数的排名规则 #

你原有的排名逻辑会让相同分数的用户拿到相同排名（比如两个100分的都排第1），如果需要相同分数也按用户名排序区分排名（比如把1、2、2、4变成1、2、3、4），可以修改排名子查询：

SELECT 
    username,
    1 + (SELECT COUNT(*) FROM spoints a WHERE a.points > b.points OR (a.points = b.points AND a.username < b.username)) AS rank
FROM spoints b
ORDER BY rank;

内容的提问来源于stack exchange，提问作者CoolColon