计数多少记录属于n到n关系中的2类

Question

我有一个数据库，其中包含的项目的模式大致如下：

COLLECTIONS ( PK id )
> 1 : n >
TOPICS ( PK id, FK collection_id )
> n : n >
TOPICS_TO_ARTICLES( FK topic_id, FK article_id )
> n : n >
ARTICLES ( PK id)

我需要的是找到一种方法来理解文章和集合之间的关系，其中一个指标是“有多少文章属于”集合1“和”集合2“。我以为我找到了一个有用的查询，但我想我遗漏了一些东西。下面是我的疑问：

SELECT
  COUNT(*) AS total
FROM
  topics_to_articles AS TTA
INNER JOIN
  topics AS T ON T.id = TTA.topic_id
INNER JOIN
  articles AS A ON TTA.article_id = A.id
WHERE
  T.collection_id = cat_1_id AND A.status = 1 AND TTA.article_id IN(
  SELECT
    TTA2.article_id
  FROM
    topics_to_articles AS TTA2
  INNER JOIN
    topics AS T2 ON T2.id = TTA2.topic_id
  INNER JOIN
    articles AS A2 ON TTA2.article_id = A2.id
  WHERE
    T2.collection_id = cat_2_id AND A2.status = 1
)

我很确定这个查询是否正常，但是我注意到，如果我反转两个"collection_id“变量(比如c1 =1和c2 =2，然后c2 =1和c1 = 2)，我会得到两个不同的结果。

有什么办法能得到这些信息吗？

Answer 1

当您将topics连接到topics_to_articles到articles时，每一篇文章都可能绑定到多个主题。并且，多个主题可能与每个集合相关联。因此，您可能要数同一篇文章多次。

例如：

• 集合a与主题a、b、c相关联。
• 集合B与主题b，d，e有关。
• 第1条与专题a、b、e有关。
• 第2条与专题a、c有关。
• 第3条与专题b、d、e有关

如果cat_1_id为1，cat_2_id为2：

• IN子句将包含项目ID 1和3(当查询不止一次列出它们时，IN子句并不关心ID被列出多少次，不管它是否在那里)
• 主查询将计数：
  • 第1条，专题a
  • 第1条，专题b
  • 第1条，专题c
  • 第3条，专题b

如果cat_1_id为2，cat_2_id为1：

• IN子句将包含项目IDs 1、2和3。
• 主查询将计数：
  • 第1条，专题b
  • 第1条，专题e
  • 第3条，专题b
  • 第3条，专题d
  • 第3条，专题e

第一个查询给您4；第二个查询给您5；您应该得到2(因为返回了两篇不同的文章)。

而且，这个词就是解决方案- DISTINCT

将两行更改为：

SELECT
  COUNT(DISTINCT A.article_id) AS total

这样，当同一个article_id多次出现时，它仍然只被计算一次。