时间戳

首页 > 解决方案 > 使用多个条件连接多对多表

sql - 使用多个条件连接多对多表

问题描述

我正在使用 sqlite3 并尝试选择具有任何（或全部）给定标签的所有文章。

CREATE TABLE article (
    id INTEGER NOT NULL,
    title TEXT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (title)
);
CREATE TABLE tag (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (name)
);
CREATE TABLE drill_to_tag (
    tag_id INTEGER NOT NULL,
    article_id INTEGER NOT NULL,
    PRIMARY KEY (tag_id, article_id),
    FOREIGN KEY(tag_id) REFERENCES tag (id),
    FOREIGN KEY(article_id) REFERENCES article (id)
);

假设标签 id 4 是“新闻”，标签 id 5 是“欧洲”，标签 id 6 是“美国”。

我可以使用以下方法获取标签 id 为 4 的文章：

select a.title from article a
  inner join article_to_tag
    on a.id = article_to_tag.article_id
    where article_to_tag.tag_id = 4;

但我真正想要的是一种让文章出现在带有标签 4 和 5 的多对多表中的方法——欧洲新闻。

这个查询可以做到这一点，但它看起来很难看？

select a.id, a.title from article a
  inner join article_to_tag atag1
    on a.id = atag1.article_id
  inner join article_to_tag atag2
    on a.id = atag2.article_id
  where atag1.tag_id = 4 and atag2.tag_id = 5;

而这个看起来更丑。

select a.id, a.title from article a
  where
    a.id in (select article_id from article_to_tag where article_id = 4)
      and
    a.id in (select article_id from article_to_tag where article_id = 5);

有没有更好的连接类型或其他方式来形成这个查询？

标签： sqlsqlitejoinmany-to-many

解决方案

您的尝试很好（几乎没有错别字）：您可以使用多个joins 或in带有子查询的条件。

另一种非常接近该in技术的方法对每个标签 ID 使用exists条件：

select a.*
from article a
where 
    exists (
        select 1 from article_to_tag at where at.article_id = a.article_id and at.tag_id = 4
    ) and exists (
        select 1 from article_to_tag at where at.article_id = a.article_id and at.tag_id = 5
    )

对于所有这些查询（joins、in、exists），您确实需要在article_to_tag(article_id, tag_id).

更简洁的方法是使用带有having子句的聚合和过滤器：

select a.id, a.title
from article a
inner join article_tag at on at.article_id = a.article_id
where at.tag_id in (4, 5)
group by a.id, a.title
having count(ditinct at.tag_id) = 2

这更容易修改以考虑更多标签，但是您需要根据您的真实数据评估此解决方案的性能；聚合往往会减慢大型数据的速度。

推荐阅读