一对多表中的sql选择

Question

我有 3 个表，其中包含以下列：

Topics:
[TopicID] [TopicName]

Messages:
[MessageID] [MessageText]

MessageTopicRelations
[EntryID] [MessageID] [TopicID]

消息可以涉及多个主题。问题是：给定几个主题，我需要获取关于所有这些主题的消息，而且不少于，但它们也可以是关于其他一些主题的。与其中一些给定主题相关的消息将不包括在内。我希望我很好地解释了我的要求。否则我可以提供样本数据。谢谢

Answer 1

以下使用 x、y 和 z 代表主题 ID，但未提供任何示例。

使用 JOIN：

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
  JOIN TOPICS tx ON tx.topicid = mtr.topicid
                AND tx.topicid = x
  JOIN TOPICS ty ON ty.topicid = mtr.topicid
                AND ty.topicid = y
  JOIN TOPICS tz ON tz.topicid = mtr.topicid
                AND tz.topicid = z

使用 GROUP BY/HAVING COUNT(*)：

  SELECT m.*
    FROM MESSAGES m
    JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
    JOIN TOPICS t ON t.topicid = mtr.topicid
   WHERE t.topicid IN (x, y, z)
GROUP BY m.messageid, m.messagetext
  HAVING COUNT(*) = 3

在这两种方法中，JOIN 方法更安全。

GROUP BY/HAVING 依赖于 MESSAGETOPICRELATIONS.TOPICID 作为主键的一部分，或者具有唯一键约束以确保不存在重复项。否则，您可能有 2 个以上的同一主题实例与一条消息关联 - 这将是误报。使用 HAVING COUNT(DISTINCT ... 会清除任何误报，但支持取决于数据库 - MySQL 在 5.1+ 上支持它，但在 4.1 上不支持。Oracle 可能必须等到周一才能看到在 SQL Server 上进行测试...

我查看了 Bill 的关于不需要加入 TOPICS 表的评论：

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
                                AND mtr.topicid IN (x, y, z)

...将返回误报 - 与 TOPICS 表中定义的至少一个值匹配的行code>IN 子句。并且：

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
                                AND mtr.topicid = x
                                AND mtr.topicid = y
                                AND mtr.topicid = z

...根本不会返回任何内容，因为 topicid 永远不可能同时包含所有值。

Answer 2

这是一个非常不优雅的解决方案

SELECT
     m.MessageID
    ,m.MessageText
FROM
    Messages m
WHERE
    m.MessageID IN (
    SELECT
        mt.MessageID
    FROM
        MessageTopicRelations mt
    WHERE
        TopicID IN (1,4,5)// List of topic IDS
    GROUP BY
        mt.MessageID
    HAVING
        count(*) = 3 //Number of topics
    )

Answer 3

编辑：感谢@Paul Creasey 和@OMG Ponies 发现我方法中的缺陷。
正确的方法是对每个主题进行自连接；如主要答案所示。

---

另一个极其不雅的条目：

select m.MessageText
       , t.TopicName
  from Messages m
       inner join MessageTopicRelations mtr
       on mtr.MessageID = m.MessageID
       inner join Topics t
       on t.TopicID = mtr.TopicID
   and
       t.TopicName = 'topic1'

UNION 

select m.MessageText
       , t.TopicName
  from Messages m
       inner join MessageTopicRelations mtr
       on mtr.MessageID = m.MessageID
       inner join Topics t
       on t.TopicID = mtr.TopicID
   and
       t.TopicName = 'topic2'
...

Answer 4

回复：OMG Ponies 的回答，您不需要加入 TOPICS 表。 HAVING COUNT(DISTINCT) 子句在 MySQL 5.1 中运行良好。我刚刚测试过。

这就是我的意思：

使用 GROUP BY/HAVING COUNT(*)：

  SELECT m.*
    FROM MESSAGES m
    JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
   WHERE mtr.topicid IN (x, y, z)
GROUP BY m.messageid
  HAVING COUNT(DISTINCT mtr.topicid) = 3

我建议 COUNT(DISTINCT) 的原因是，如果列 (messageid,topicid) 不存在如果没有唯一约束，则可能会出现重复项，这会导致组中的计数为 3，即使不同值少于三个也是如此。