从一对多表中选择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)

...将返回误报 - 与 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 永远不可能同时是所有值。