兼容SQLite/MySQL的多表关联：查询标签为用户订阅子集的posts_id

要实现“找出所有标签均为指定用户订阅标签子集的帖子”的需求，可通过以下两种方式完成，均能在SQLite、MySQL和MariaDB中正常运行：

方案一：分组统计判断 #

SELECT p.posts_id
FROM posts p
INNER JOIN posts_tags pt ON p.posts_id = pt.post_id
LEFT JOIN users_tags ut 
    ON pt.tags_id = ut.tags_id 
    AND ut.user_id = 2
GROUP BY p.posts_id
HAVING COUNT(pt.tags_id) = COUNT(ut.tags_id);

逻辑说明 #

• 关联修正：原代码中posts_tags的关联字段是post_id而非posts_id，改用ON显式关联避免字段名不匹配问题；
• 左连接用户标签：用LEFT JOIN关联目标用户（此处为ID=2）的订阅标签，帖子标签若不在用户订阅列表中，对应ut.tags_id会返回NULL；
• 分组统计判断：
  • COUNT(pt.tags_id)：统计当前帖子的总标签数量；
  • COUNT(ut.tags_id)：统计当前帖子中属于用户订阅标签的数量（NULL值不会被COUNT统计）；
  • 当两者数值相等时，说明该帖子所有标签都在用户订阅列表中，符合筛选条件。

方案二：NOT EXISTS子查询 #

若偏好更直观的逻辑表达，可使用双重NOT EXISTS判断：

SELECT p.posts_id
FROM posts p
WHERE NOT EXISTS (
    SELECT 1
    FROM posts_tags pt
    WHERE pt.post_id = p.posts_id
    AND NOT EXISTS (
        SELECT 1
        FROM users_tags ut
        WHERE ut.tags_id = pt.tags_id
        AND ut.user_id = 2
    )
);

逻辑说明 #

• 外层NOT EXISTS用于筛选“不存在未被用户订阅标签”的帖子；
• 内层NOT EXISTS判断帖子是否包含用户未订阅的标签，若存在则该帖子会被排除。

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