php - MySQL SELECT 前 10 名并按此值排序

Question

这是我在开发排名系统时遇到的一个有趣的问题。

三个表:

1. 用户(id，年龄，...)
2. 路线(id，难度，...)
3. 日志(id、user_id、route_id、点数、日期……)

---

目标是生成一个用户列表，该列表按用户在前 10 个日志中获得的点数排序。对于每个用户，我必须找到他/她的前 10 个日志(按点排序)，然后按这个数字对所有用户进行排序。

我还必须能够限制其他参数考虑的路线和用户 -> 用户年龄、路线难度、日志日期。例如，从所有 25 岁以上的用户中创建此排名列表，这些用户添加了难度为 1-8 的路线，时间不超过一个月(日志)。

到目前为止，这是我所知道的: 1. 我知道如何根据总积分来选择和排序用户:

SELECT 
    l.id, l.idr,
    u.name,..., ...,
    SUM(l.points) as totalPoints
FROM logs l
INNER JOIN routes r ON l.idr = r.id
INNER JOIN users u ON d.idu = u.id
WHERE
  /*
  All the conditions I need
  */

GROUP BY u.id
ORDER BY totalPoints DESC

1. 为一位用户选择前 10 名:

   SELECT SUM(points)
   FROM (
       SELECT l.points as points
       FROM logs l
       INNER JOIN users u ON l.idu = u.id
       WHERE u.id = '1'
       LIMIT 10
   ) AS T
   

我只是不知道如何有效地将它们组合在一起。我已经通过一些临时表解决了这个问题，并在 PHP 的 while 循环中请求了额外的数据，但这非常缓慢且效率低下。随着数据库越来越大(日志 ~ 40 000，用户 ~ 2 000，路由 ~ 1 000 条记录)，需要更有效的解决方案。

正如我提到的，我正在使用 PHP，所以如果你知道如何使它更快(更有效)，而不是通过创建一个很棒的查询，而是通过一些更聪明的小查询和一些 PHP，那真的是科尔也是。

感谢任何想法:)

Answer 1

考虑一个相关计数子查询来计算一个排名列，然后您可以将其用作外部查询中的过滤器。如果 MySQL 支持窗口函数，通常的方法是RANK OVER() CTE 解决方案。

下面是用户，然后是用户和路由的两个例子。请注意，对于每个分组级别，您都添加了 WHERE 子句来对 t 派生表中的子查询和 GROUP BY 列进行排名。这同样适用于每个特殊的 WHERE 条件，因为它们必须在子查询中进行镜像。

用户 (每个用户的前 10 个总日志)

SELECT main.`user`, main.totalPoints
FROM
  (SELECT t.name as `user`, SUM(t.points) as totalPoints
   FROM
      (SELECT l.id, l.idr, u.name, l.points,
             (SELECT Count(*) FROM logs sub
              WHERE sub.idu = l.idu
              AND sub.points >= l.points) AS user_rank 
       FROM logs l
       INNER JOIN users u ON l.idu = u.id) AS t
   WHERE t.user_rank <= 10
   GROUP BY t.name) AS main
ORDER BY main.totalPoints

用户和路由 (每个用户每个路由的前 10 个日志)

SELECT main.`user`, main.route, main.totalPoints
FROM
  (SELECT t.name as `user`, t.idr as route, SUM(t.points) as totalPoints
   FROM
      (SELECT l.id, l.idr, u.name, l.points,
             (SELECT Count(*) FROM logs sub
              WHERE sub.idu = l.idu
              AND sub.idr = l.idr
              AND sub.points >= l.points) AS user_route_rank 
       FROM logs l
       INNER JOIN routes r ON l.idr = r.id
       INNER JOIN users u ON l.idu = u.id) AS t
   WHERE t.user_route_rank <= 10
   GROUP BY t.name, t.idr) AS main
ORDER BY main.totalPoints

注意事项:

1. TIES:此方法中包含记录点。有一些用于决胜局的方法，例如采用最低主键，如下所示:

   (SELECT Count(*) FROM logs sub
    WHERE sub.idu = l.idu
    AND sub.idr = l.idr
    AND (sub.points >= l.points
         OR sub.points = l.points AND sub.id <= l.id))  AS user_route_rank 
   

2. GROUP BY:您上面不完整的 GROUP BY 聚合已被删除。 MySQL 允许它但在所有其他 RDMS 中都会失败。你有没有ANSI or ONLY FULL GROUP BY mode上，MySQL 会引发错误，因为聚合查询的 SELECT 子句中的非聚合列也必须包含在 GROUP BY 中(尽管反向在 ANSI 中有效) .