mysql - 我应该把 where 子句放在这个查询的什么地方来过滤结果？

Question

我试图在查询中插入一个 where 子句以仅选择某些行。

这是查询:

SELECT m.MapName, SEC_TO_TIME(TRUNCATE(t.Time,3)) AS Time, p.User
FROM times t INNER JOIN
     maps m INNER JOIN
     players p
     ON t.MapID = m.MapID INNER JOIN
     (SELECT t.MapId, MIN(t.time) as time
      FROM times t
      GROUP BY t.MapId
     ) tmin
     ON tmin.MapId = t.MapId and tmin.time = t.time and p.PlayerID = t.PlayerID;

我想做的是添加一个 WHERE 子句，也只选择表 times< 中 style = 0 和 type = 0 的时间.

当前运行的查询总是以以下形式返回:

MapName:       Time:            PlayerID:
map_name       00:00:09.440     player_name1
map_name2      xx:xx:xx:xxx     player_name2
map_name3      xx:xx:xx:xxx     player_name3

如果这有帮助，times 表的布局如下所示:

MapID:      Type:      Style:      Time:
1           0          0           214.235
2           1          0           12.325
3           0          1           321.333

我想做的是与上面相同的查询，但只选择 type 和 style 为 0 的时间。

Answer 1

试试这个:

SELECT m.MapName, SEC_TO_TIME(TRUNCATE(t.Time,3)) AS Time, p.User
FROM times t 
INNER JOIN maps m ON t.MapID = m.MapID 
INNER JOIN players p ON p.PlayerID = t.PlayerID
INNER JOIN (SELECT t.MapId, MIN(t.time) as time
            FROM times t
            WHERE t.style = 0 and t.type = 0 
            GROUP BY t.MapId
          ) tmin ON tmin.MapId = t.MapId and tmin.time = t.time;