php - MySQL 选择根据表中的条目数创建排名...怎么做？

Question

我有什么:

Table 1 : USERS (autoID, name, etc)

Table 2 : TROPHIES (autoID, name, etc)

Table 3 : VIEWS (userID, timestamp, etc)

Table 4 : CANDIDATES (userID, trophyID, etc)

我所知道的:USERS.autoID & TROPHIES.autoID

我是怎么做到的:

我有一张 TROPHIES 表，我在其中存储可以指定用户的不同类别。

每个用户都可以从 TROPHIES 表中提名 1,2 个或更多奖杯。

在 VIEWS 表中，我使用 USERS.autoID、时间戳和其他数据存储每个用户个人资料的每个 View 。

在 CANDIDATES 表中，我存储了 TROPHIES.autoID 和 USERS.autoID - 这样我就知道哪个用户被提名了哪个奖杯。

我需要知道的

知道 USERS.autoID 和 TROPHIES.autoID 我想根据最近 3 天的条目数制作一个 TOP，例如在 CANDIDATES 表中列出的所有用户的 VIEWS 表中，并找出特定奖杯位置在特定用户的顶部。

假设 autoID 为 1 的用户被提名为 autoID 为 10 的 TROPHY，并且在过去 3 天在 VIEWS 表中有 100 个条目，但还有其他 3 个用户被提名为 autoID 为 10 的 TROPHY，他们有最近 3 天有超过 100 个条目，所以...我需要一个返回数字 4 的选择。

我的问题:

我可以用 1 个 SELECT 查询来做到这一点吗？如果是……怎么办？如果不是...我怎样才能让这个查询花费尽可能少的资源。

谢谢!

[编辑]

这是一些数据

表 1 - 用户

+--------+-------+| autoID | name  |+--------+-------+| 1      | user1 || 2      | user2 || 3      | user3 |+--------+-------+

表 2 - 奖杯

+--------+------------+| autoID | name       |+--------+------------+| 1      | Baseball   || 2      | Basketball || 3      | Boxing     |+--------+------------+

表 3 - View

+--------+--------+------------+| autoID | userID | timestamp  |+--------+--------+------------+| 1      | 2      | 1551632970 || 2      | 2      | 1551632971 || 3      | 3      | 1551632972 || 4      | 1      | 1551632973 || 5      | 2      | 1551632974 || 6      | 1      | 1551632975 || 7      | 3      | 1551632976 || 8      | 1      | 1551632977 || 9      | 2      | 1551632978 || 10     | 3      | 1551632979 || 11     | 3      | 1551632980 || 12     | 3      | 1551632981 |+--------+--------+------------+

表 4 - 候选人

+--------+--------+----------+| autoID | userID | trophyID |+--------+--------+----------+| 1      | 2      | 1        || 2      | 3      | 3        || 3      | 1      | 2        || 4      | 1      | 1        |+--------+--------+----------+

最后，我希望能够根据 VIEWS 表中的条目了解用户对于特定奖杯的位置。

假设我想在时间戳 1551632972 之后检查 USER 的 autoID = 1 棒球(autoID = 1 的奖杯)的位置。

所以...首先我们必须查看这个奖杯中列出了哪些用户，这样我们就可以忽略表 VIEWS 中其他用户的条目。 autoID 为 1(棒球)的奖杯仅列出了两个用户 - user1 和 user2。

现在我想看看有多少条目，这样我就可以找出 user1 在这个顶部的位置。

因此，如果我们从表 VIEWS 中选择并计算时间戳等于或大于 1551632972 的 user1 的所有条目，我们将得到数字 3，如果我们对 user2 执行相同的操作，我们将得到 2，因为 3 更大大于 2，user1 将排在第 1 位，user2 将排在第 2 位。

我正在寻找一种方法，使用单个 MySQL 查询(如果可能)或寻找最佳解决方案来为特定运动中的特定用户获得 TOP 位置...

Answer 1

我找到了解决方案...我会把它留在这里以防其他人需要它。

SELECT 
U.autoId, 
U.name,
U1.position

FROM USERS U
JOIN (SELECT 
@rownum := @rownum + 1 AS position,
U.autoId, 
U.name,

COUNT(V.autoID) as "Nr"

FROM USERS U
JOIN VIEWS V ON V.userID= U.autoID
JOIN CANDIDATESC ON C.userID= U.autoID

JOIN (SELECT @rownum := 0) R

WHERE C.trophyID= 'id_of_trophy_wanted' GROUP BY U.autoID ORDER BY Nr DESC) as U1 ON U1.autoID = 
U.autoID

WHERE U.autoID = 'id_of_user_wanted'

感谢那些试图提供帮助的人!