时间戳

首页 > 解决方案 > 从包含连接的 MIN 查询中选择 ID

mysql - 从包含连接的 MIN 查询中选择 ID

问题描述

对不起标题，标题很差！

我有这些相关的表格：

Times
+--------+----------+-------------+------+---------+
| TimeID | PlayerID | MapCourseID | Mode | RunTime |
+--------+----------+-------------+------+---------+
Checkpoints
+--------------+--------+------------+---------+
| CheckpointID | TimeID | Checkpoint | RunTime |
+--------------+--------+------------+---------+
Maps
+-------+------+
| MapID | Name |
+-------+------+
MapCourses
+-----------+-------+--------+
| MapCourse | MapID | Course |
+-----------+-------+--------+

RunTime 将它们的“时间”存储为一个 int

MapID我有一个查询，可以为所有课程和所有模式中的特定地图选择最快时间

SELECT MIN(Times.RunTime), MapCourses.Course, Times.Mode 
FROM Times 
INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID 
INNER JOIN Players ON Players.PlayerID=Times.PlayerID
WHERE Players.Cheater=0 AND MapCourses.MapID=%d 
GROUP BY MapCourses.Course, Times.Mode;

哪个工作正常，但现在我想进行另一个查询，选择最快时间的检查点，因为检查点与 TimeID 相关联

我试过做这样的事情

SELECT * FROM Checkpoints 
  INNER JOIN (
    SELECT 
        MIN(Times.RunTime), 
        MapCourses.Course, 
        Times.Mode, 
        MapCourses.MapID, 
        Players.SteamID32 
    FROM 
      Times 
      INNER JOIN MapCourses ON MapCourses.MapCourseID = Times.MapCourseID 
      INNER JOIN Players ON Players.PlayerID = Times.PlayerID
    WHERE 
      Players.Cheater = 0 
      AND MapCourses.MapID = %d 
    GROUP BY 
      MapCourses.Course, 
      Times.Mode
   ) AS wrs ON Checkpoints.TimeID = wrs.TimeID

还尝试过这样的事情：

SELECT 
  Checkpoints.RunTime, 
  Checkpoints.Checkpoint, 
  MapCourses.Course, 
  Times.Mode, 
  Times.TimeID, 
  Players.Alias 
FROM 
  Checkpoints 
  INNER JOIN Times ON Times.TimeID = Checkpoints.TimeID 
  INNER JOIN MapCourses ON MapCourses.MapCourseID = Times.MapCourseID 
  INNER JOIN Players ON Players.PlayerID = Times.PlayerID
WHERE 
  Players.Cheater = 0 
  AND MapCourses.MapID = %d 
  AND Times.RunTime = (
    SELECT 
      MIN(Times.RunTime) 
    FROM 
      Times 
    WHERE 
      Times.MapCourseID = MapCourses.MapCourseID 
      AND Mode = Times.Mode
  )

似乎都没有真正起作用，任何帮助都会很棒，谢谢！

基本上，如果我正在处理地图 id 50，我已经有一个查询可以在所有模式和课程的地图 id 50 上获得最快的时间，我正在尝试构建的查询是一个获得最快检查点的查询地图 id 50 上每门课程和模式的时间

标签： mysql

解决方案

如果我正确地遵循了这一点，您是否希望每个检查点的最短 Checkpoints.TimeID 格式与上述类似？

SELECT MIN(Checkpoints.TimeID), MapCourses.Course, Checkpoints.Checkpoint, Times.Mode 
FROM Checkpoints
INNER JOIN Times ON Checkpoints.TimeID=Times.TimeID
INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID 
INNER JOIN Players ON Players.PlayerID=Times.PlayerID
WHERE Players.Cheater=0 AND MapCourses.MapID=%d 
GROUP BY MapCourses.Course, Checkpoints.Checkpoint, Times.Mode;

显然无法访问您的数据库我无法对此进行测试，但是，这似乎是您要问的？

[编辑]

啊，所以你想保持最小时间，但想在返回值中添加检查点：

SELECT MIN(Times.RunTime), MapCourses.Course, Checkpoints.Checkpoint, Times.Mode
FROM Times 
INNER JOIN MapCourses ON MapCourses.MapCourseID=Times.MapCourseID 
INNER JOIN Players ON Players.PlayerID=Times.PlayerID
INNER JOIN Checkpoints ON Checkpoints.TimeID=Times.TimeID
WHERE Players.Cheater=0 AND MapCourses.MapID=%d 
GROUP BY MapCourses.Course, Checkpoints.Checkpoint, Times.Mode;

所以这应该返回你最快的时间，地图，检查点，然后是模式。

推荐阅读