选择相关表中前 N 个结果的项目

Question

假设我有一个游戏，其中提出一个问题，人们发布回答并进行评分，前 10 名回答获胜。我有一个存储所有这些信息的 SQL 数据库，因此我可能有诸如用户、问题和响应之类的表。 Responses 表具有外键 user_id 和 Question_id 以及属性 Total_score。

显然，对于特定问题，我可以检索带有顺序和限制的前 10 个响应：

SELECT * FROM Responses WHERE question_id=? ORDER BY total_score DESC LIMIT 10;

我正在寻找一种方法，可以为特定用户确定其所有获胜者响应的列表（在顶部） 10 针对他们的特定问题）。以编程方式单步执行每个响应并查看它是否包含在其问题的前 10 个中是很简单的，但我想对此进行优化，因此我不会执行 N+1 查询，其中 N 是用户已提交的响应数。

Answer 1

如果您使用 Oracle、Microsoft SQL Server、DB2 或 PostgreSQL，这些数据库支持窗口函数。将用户的回答加入到同一问题的其他回答中。然后按问题分区，按分数降序排列。使用每个分区中的行号将集合限制为前 10 名中的行号。同时传递给定用户的 user_id，以便您可以从前 10 名中挑选他们，因为您只对给定用户的响应感兴趣。

SELECT *
FROM (
  SELECT r1.user_id AS given_user, r2.*,
    ROW_NUMBER() OVER (PARTITION BY r2.question_id ORDER BY r2.total_score DESC) AS rownum
  FROM Responses r1 JOIN Responses r2 ON r1.question_id = r2.question_id
  WHERE r1.user_id = ?
) t
WHERE rownum <= 10 AND user_id = given_user;

但是，如果您使用 MySQL 或 SQLite 或其他不支持窗口函数的数据库，则可以使用以下不同的解决方案：

查询用户的响应，并使用联接来匹配分数更高（或更早）的相应问题的其他响应平局的情况下PK）。按问题分组，并计算得分较高的回复数量。如果计数小于 10，则用户的回答属于每个问题的前 10 名。

SELECT r1.*
FROM Responses r1
LEFT OUTER JOIN Responses r2 ON r1.question_id = r2.question_id 
  AND (r1.total_score < r2.total_score 
    OR r1.total_score = r2.total_score AND r1.response_id > r2.response_id)
WHERE r1.user_id = ?
GROUP BY r1.question_id
HAVING COUNT(*) < 10;

Answer 2

尝试嵌入 select 语句。我今天无法访问数据库工具，因此无法确认语法/输出。只需进行适当的更改即可捕获您需要的所有列。您还可以向主查询添加问题并连接响应。

select *
  from users
     , responses
 where users.user_id=responses.user_id
   and responses.response_id in (SELECT z.response_id 
                                   FROM Responses z 
                                  WHERE z.user_id = users.user_id 
                                 ORDER BY total_score DESC 
                                 LIMIT 10)

Answer 3

或者您可以通过添加另一个字段（例如“IsTopPost”）来真正优化它。当有人投票时，您必须更新热门帖子，但您的查询很简单：

SELECT * FROM Responses WHERE user_id=? and IsTopPost = 1

Answer 4

我认为这样的事情应该可以解决问题：

SELECT 
    user_id, question_id, response_id 
FROM 
    Responses AS r1 
WHERE 
    user_id = ?
AND
    response_id IN (SELECT response_id 
                    FROM Responses AS r2 
                    WHERE r2.question_id = r1.question_id 
                    ORDER BY total_score DESC LIMIT 10)

实际上，对于每个 Question_id，执行一个子查询来确定该 Question_id 的前 10 个响应。

您可能需要考虑添加一列，将某些响应标记为“获胜者”。这样，您只需选择这些行即可使数据库免于一遍又一遍地计算前 10 行。