给定一个 ID，返回与给定 ID 类别不同的数据

Question

我有3个表：视频，video_category和video_category_join。

一个视频可以有多个video_category，一个video_category可以与多个视频相关联：因此video_category_join表

表：视频

| video_id | title | description | duration |
|--------------------------------------------
| 12       | hello | ...         | 190      |
| 24       | hello | ...         | 190      |
| 78       | hello | ...         | 190      |
| 34       | hello | ...         | 190      |
| 91       | hello | ...         | 190      |
| 88       | hello | ...         | 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                 |
| 88       | 1                 |
| 91       | 6                 |
| 91       | 5                 |
| 78       | 4                 |

给定一个video_id，我想选择与给定video_id类别不同的随机视频。

因此，例如，如果video_id为 24，则结果集应返回与 video_id 24 类别不同的视频。在这种情况下，唯一与video_id 24 类别不同的视频是：video_id 88。

这是一个我认为可以工作的查询，但如果给定的video_id没有类别，则不返回任何内容。

SELECT DISTINCT v.video_id, v.title, v.duration, 2 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 = 5
ORDER BY RAND()
LIMIT 20

Answer 1

一种方法使用自连接和聚合。 连接匹配类别;having返回不共享任何类别的视频：

select vc.video_id
from video_category vc left join
     video_category vc2
     on vc2.video_category_id = vc.video_category_id and
        vc2.video_id = 5
group by vc.video_id
having count(vc2.video_id) = 0;

Answer 2

你可以用联接来做到这一点

select * from video where video_id not in (
  select distinct v.video_id from video v
  left join video_category_join vc
  on v.video_id = vc.video_id
  where vc.video_category_id in (
    select video_category_id
    from video_category_join
    where video_id = 24)
 )
 order by rand()
 limit 5

这将选择 5 个与视频 #24 不属于同一类别的随机视频