我发现现有问题在多个字段上有最新/最低的单行，或者在单个字段上有最新的 n 行，但不是两者一起出现

我需要构造 2 个查询，使用这个示例表(类似于 this question)

CREATE TABLE lap_data (
     id            int(1)     NOT NULL,
     track_id      int(1)     NOT NULL,
     user_id       int(1)     NOT NULL,
     lap_time      time       NOT NULL,
     lap_status    tinyint(1) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `lap_data` (`id`, `track_id`, `user_id`, `lap_time`, `lap_status`) VALUES
(1, 1, 1, '04:18:23', 1),
(2, 2, 2, '01:09:54', 1),
(3, 2, 1, '01:05:30', 1),
(4, 1, 2, '04:17:02', 1),
(5, 3, 2, '01:13:10', 1),
(6, 4, 1, '01:36:59', 0),
(7, 3, 2, '01:18:10', 1),
(8, 2, 3, '01:06:42', 1),
(9, 1, 1, '04:16:12', 1),
(10, 1, 2, '04:18:12', 1),
(11, 2, 3, '01:03:20', 1),
(12, 2, 1, '01:08:13', 1),
(13, 2, 1, '01:09:44', 1),
(14, 3, 2, '01:14:10', 1),
(15, 3, 2, '01:17:20', 1),
(16, 4, 1, '01:36:23', 1),
(17, 2, 1, '01:11:34', 1);

Query 1:

• I want the top 2 results by lap_time - per track, per user
  • so if a given user_id has 20 records spread over 3 tracks...
    • I would expect 6 results for that user_id
    • or 5 if one of those tracks only has a single record
  • the same lap time at 2 different tracks should both be displayed, not appear just once
• I want to use a WHERE user_id IN (..) for part 1
• ordered by a given field, ie lap_time
• where lap_status = 1

This is the query I've been using elsewhere to collect the two latest records of a given type, which is the closest to what I think I need, modified to this example .. although unfinished, as in the other instance, it was only collecting the 2 most recent rows for each user_id .. here I need it to take track_id into account as well

 SELECT
 * 
 FROM (
     SELECT
     t.id,
     t.track_id,
     t.user_id,
     t.lap_time,
     t.lap_status,
     @row:=case WHEN @prev=user_id THEN @row ELSE 0 END +1 rn,
     @prev:=user_id
     FROM `lap_data` t
     CROSS JOIN (SELECT @row:=0, @prev:=null) c
     WHERE t.user_id IN (1,2)
     ORDER BY user_id, id DESC
 ) src
 WHERE rn <= 2
 ORDER BY lap_time DESC

查询 1 所需结果:( WHERE user_id IN (1,2) )

|  id  |  track_id  |  user_id  |  lap_time  |  lap_status  |
--------------------------------------------------------------
|   9  |         1  |        1  |  04:16:12  |           1  |
|   1  |         1  |        1  |  04:18:23  |           1  |
|   3  |         2  |        1  |  01:05:30  |           1  |
|  12  |         2  |        1  |  01:08:13  |           1  |
|  16  |         4  |        1  |  01:36:23  |           1  |
|   6  |         4  |        1  |  01:36:59  |           1  |
|   4  |         1  |        2  |  04:17:02  |           1  |
|  10  |         1  |        2  |  04:18:12  |           1  |
|   2  |         2  |        2  |  01:09:54  |           1  |
|   5  |         3  |        2  |  01:13:10  |           1  |
|  14  |         3  |        2  |  01:14:10  |           1  |

查询 2:

• 我想为单个用户得到上面的逆
  • 即排除前两个结果的其余部分，仅针对特定 user_id

查询 2 所需结果:(WHERE user_id = '1')

|  id  |  track_id  |  user_id  |  lap_time  |  lap_status  |
--------------------------------------------------------------
|  13  |         2  |        1  |  01:09:44  |           1  |
|  17  |         2  |        1  |  01:11:34  |           1  |

有什么建议吗？谢谢!

最佳答案

我之前回答的MYSQL版本:

SELECT 
lt.id,
lt.user_id,
lt.track_id,
lt.lap_status,
lt.lap_time 
FROM (
SELECT id,
    user_id,
    track_id,
    lap_status,
    lap_time,
    CASE WHEN track_id = @prevTrackId AND user_id = @prevUserId THEN @curRank := @curRank + 1 ELSE @curRank := 1 END Rank,
    @prevTrackId := track_id,
    @prevUserId := user_id
FROM lap_data, (SELECT @curRank := 0, @prevTrackId := 1, @prevUserId := 1) r
ORDER BY user_id,track_id,lap_time ASC
) lt 
WHERE Rank <= 2

关于mySQL - 选择前 n 行，按 2 列分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39502759/