关联表中如何筛选同时属于指定多个分类的视频

要找出同时属于category_id=1和category_id=2的视频，排除仅属于其中一个或均不属于的情况，这里提供几种实用的SQL实现方法：

方法一：分组统计法（最常用） #

通过分组统计每个视频关联的目标分类数量，筛选出数量等于目标分类总数的视频：

-- 先获取符合条件的视频ID
SELECT video_id
FROM Categories_Videos
WHERE category_id IN (1, 2)
GROUP BY video_id
HAVING COUNT(DISTINCT category_id) = 2;

如果需要获取视频的完整信息，只需关联Videos表：

SELECT v.*
FROM Videos v
INNER JOIN (
    SELECT video_id
    FROM Categories_Videos
    WHERE category_id IN (1, 2)
    GROUP BY video_id
    HAVING COUNT(DISTINCT category_id) = 2
) qualified_videos ON v.id = qualified_videos.video_id;

使用COUNT(DISTINCT)是为了避免同一视频重复关联同一分类的异常情况；如果你的Categories_Videos表能保证一个视频对应一个分类仅存一条记录，也可以简化为COUNT(*) = 2。

方法二：自连接法 #

通过两次关联Categories_Videos表，分别匹配两个目标分类：

SELECT DISTINCT v.*
FROM Videos v
INNER JOIN Categories_Videos cv1 
    ON v.id = cv1.video_id AND cv1.category_id = 1
INNER JOIN Categories_Videos cv2 
    ON v.id = cv2.video_id AND cv2.category_id = 2;

这种方法逻辑直白易懂，适合需要匹配的分类数量较少的场景。

方法三：EXISTS子查询法 #

通过两次子查询验证视频是否同时存在于两个分类的关联记录中：

SELECT v.*
FROM Videos v
WHERE EXISTS (
    SELECT 1 
    FROM Categories_Videos cv 
    WHERE cv.video_id = v.id AND cv.category_id = 1
)
AND EXISTS (
    SELECT 1 
    FROM Categories_Videos cv 
    WHERE cv.video_id = v.id AND cv.category_id = 2
);

如果Categories_Videos表在video_id和category_id上建有联合索引，这种方法的查询效率会比较出色。

内容的提问来源于stack exchange，提问作者RunyanAvenue