mysql - 如何从 UNION 查询返回数据

Question

我有 3 个表:video、video_category 和 video_category_join。

一个视频可以有多个 video_category，并且一个 video_category 可以在多个视频中关联:因此 video_category_join 表

表格:视频

| video_id | title   | duration |
|-------------------------------|
| 12       | title12 | 190      |
| 24       | title24 | 190      |
| 78       | title78 | 190      |
| 34       | title34 | 190      |
| 91       | title91 | 190      |
| 88       | title88 | 190      |

表格:video_category

| video_category_id | name    |
|-----------------------------|
| 4                 | music   |
| 8                 | tv      |
| 5                 | black   |
| 2                 | politic |
| 1                 | movie   |
| 6                 | other   |

表格:video_category_join

| video_id | video_category_id |
|------------------------------|
| 24       | 4                 |
| 24       | 5                 |
| 78       | 4                 |
| 78       | 5                 |
| 88       | 1                 |
| 91       | 4                 |
| 91       | 1                 |

给定一个 video_id，我想返回 20 个与 video_id 具有相同类别的视频。

如果结果集返回的视频少于 20 个，我希望能够通过选择随机视频进行补偿，直到达到 20 个视频(优先级始终是选择具有相同类别的视频，如有必要，随机视频，直到我最多 20 个视频)。

因此，如果video_id = 24，则结果集将返回

| video_id |
|----------|
| 78       |
| 91       |
| 12       |
| 88       |
| 34       |

video_id 74 和 91 与 video_id 24 具有相同的类别，因此始终位于顶部。然后，要获取最多 20 个视频，结果集将返回随机 video_id 12、34 和 91。

我问了很多问题，发现我需要 UNION 类型的查询。这是我根据我在这里提出的问题构建的内容...我快到了，我只需要完成最后一个查询即可获取 video_id 12、88 和 34

SELECT video_id, title, duration
FROM (
    (
     SELECT v.video_id, v.title, v.duration, 1 AS preferred
    FROM video_category_join vc
    JOIN video_category_join vc2 ON vc2.video_category_id = vc.video_category_id AND vc2.video_id <> vc.video_id
    JOIN video v ON v.video_id = vc2.video_id
    WHERE vc.video_id = 24
    GROUP BY vc2.video_id
    ORDER BY RAND()
    LIMIT 20
    )
    UNION
    (
    ...
    )
) AS t1
ORDER BY preferred
LIMIT 20

Answer 1

您需要在与您搜索的视频具有相同类别的所有视频与表格的所有行之间使用 UNION ALL。
联合结果集的行将因 preferred 列的值而有所不同。
然后进行分组，仅保留常见视频的最小 preferred，最后按 preferred 和 rand() 排序:

select t.video_id, t.title, t.duration
from (
  select
    t.video_id, t.title, t.duration, min(t.preferred) preferred
  from (
    select distinct v.*, 1 preferred 
    from video v inner join video_category_join j
    on j.video_id = v.video_id
    where v.video_id <> 24
    and j.video_category_id in (
      select video_category_id
      from video_category_join
      where video_id = 24
    )
    union all
    select video_id, title, duration, 2 preferred
    from video
    where video_id <> 24
  ) t  
  group by t.video_id, t.title, t.duration
) t  
order by preferred, rand()
limit 20

请参阅demo (对于限制 4)。
结果:

| video_id | title   | duration |
| -------- | ------- | -------- |
| 91       | title91 | 190      |
| 78       | title78 | 190      |
| 34       | title34 | 190      |
| 12       | title12 | 190      |