我有三张桌子,下面有几列:
话题:
[主题名称]
信息:
[消息ID][消息文本]
消息主题关系
[entryid][messageid][topicid]
消息可以是关于多个主题的。问题是:给定两个主题,我需要得到关于所有这些主题的消息,而不是更少,但它们也可以是关于其他主题的。不会包含有关这些给定主题中某些主题的消息。我希望我能很好地解释我的要求。否则我可以提供样本数据。谢谢

最佳答案

下面使用xyz代表主题id,因为没有提供任何示例。
使用联接:

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

使用分组方式/有计数(*):
  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

在这两种方法中,连接方法更安全。
group by/having依赖于MESSAGETOPICRELATIONS.TOPICID作为主键的一部分,或者具有唯一的键约束以确保不存在重复项。否则,可以将同一主题的2+个实例关联到消息-这将是误报。使用HAVING COUNT(DISTINCT ...可以清除任何误报,但支持取决于数据库-mysql支持5.1+,但不支持4.1+。Oracle可能要等到周一才能在SQL Server上进行测试…
我查看了比尔关于不需要加入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永远不能同时返回所有值。

09-05 11:16