mysql - 选择每个组的前X条记录或默认

我有以下架构：

users:

id email
1  '[email protected]'
2  '[email protected]'

video_group:

id title
1  'Group 1'
2  'Group 2'

videos:

id group_id rank title
1  1        1    'Group 1 - Video 1'
2  1        2    'Group 1 - Video 2'
3  2        1    'Group 2 - Video 1'

user_video_play_times:

video_id user_id time last_update
2        1       12   01-02-2018
1        1       120  01-01-2018

我需要获取用户在特定组中播放的最后一个视频的time、user_id、video_id和group_id，但如果某个组的user_video_play_times上没有记录，则应返回排名最低的视频。例如：

user_id group_id video_id time
1       1        2        12    -- user.one + group 1
1       2        3        0     -- user one + group 2

这是我迄今为止的疑问：

SELECT
   pt.user_id user_id,
   v.id       video_id,
   g.id       group_id,
   pt.time    time
FROM
   videos v
   INNER JOIN video_groups g ON g.id = v.group_id
   LEFT JOIN user_video_play_times pt ON
      pt.video_id = v.id AND
      pt.user_id = 1
   LEFT JOIN (
      SELECT
         g.id AS g_id,
         MAX(pt.last_update) AS pt_last_update
      FROM
         user_video_play_times pt
         INNER JOIN videos v ON v.id = pt.video_id
         INNER JOIN video_groups g ON g.id = v.group_id
      WHERE
         pt.user_id = 1 AND
         g.id IN (1, 2)
      GROUP BY
         g.id
   ) lpt ON lpt.g_id = g.id AND lpt.pt_last_update = pt.last_update
WHERE
   g.id IN (1, 2)
GROUP BY
   g.id

有点管用，但是。。。
由于某些原因，将v.title添加到列选择会弄乱结果，使所有内容只返回排名1的视频。知道为什么吗？
这个查询是否可以优化，或者是否有其他更好的方法来实现相同的结果？
非常感谢您的帮助！
分贝小提琴here
更新1：
此问题似乎仅在text类型的列os时发生。

最佳答案

因为您的db<>fiddle是针对MariaDB 10.3版的；我想您已经有Window Functions可用了。
根据定义的规则，我们可以在Row_number()的分区上使用group_id函数来获取行号值。具有最新last_update值的视频的行号将为1，依此类推。如果没有播放视频，则排名值最小的视频的行数将为1。
我们可以将这个结果集用作派生表，只考虑行数为1的行。

SELECT
  dt.user_id,
  dt.group_id,
  dt.video_id,
  dt.video_title,
  dt.time
FROM
(
  SELECT
     pt.user_id AS user_id,
     g.id       AS group_id,
     v.id       AS video_id,
     v.title    AS video_title,
     pt.time    AS time,
     ROW_NUMBER() OVER(PARTITION BY v.group_id
                       ORDER BY pt.last_update DESC,
                                v.`rank` ASC) AS row_num
  FROM videos AS v
  INNER JOIN video_groups AS g
    ON g.id = v.group_id AND
       g.id IN (1,2)
  LEFT JOIN user_video_play_times AS pt
    ON pt.video_id = v.id AND
       pt.user_id = 1
) AS dt
WHERE dt.row_num = 1

View on DB Fiddle
结果：

| user_id | group_id | video_id | video_title       | time |
| ------- | -------- | -------- | ----------------- | ---- |
| 1       | 1        | 2        | Group 1 - Video 2 | 12   |
|         | 2        | 3        | Group 2 - Video 1 |      |

注意Rank是一个Reserved Keyword，您应该避免将它用作列/表名。

关于mysql - 选择每个组的前X条记录或默认，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53046794/