mysql - 如何 JOIN 具有一个强制条件和一个可选条件的两个表

Question

我有一个media 表和一个tag_media 表。 tag_media 表是包含列 id_tag 和 id_media 的关系表。单个媒体文件可以标记多个标签。像这样的东西:

tag_media:
    id_tag
    id_media

media:
    id_media
    (etc, etc)

我需要一个查询，允许我获取所有标记有一组强制标签和一组可选标签的媒体，这样我就可以保证返回的媒体被标记有所有强制标签，至少有一个可选标签。

我该怎么做？

Answer 1

此查询将执行您要查找的内容:

SELECT
   M.id_media
FROM
   media M
   INNER JOIN tag_media T ON M.id_media = T.id_media
WHERE T.id_tag IN ('required1', 'required2', ... 'optional1', 'optional2', ...)
GROUP BY M.id_media
HAVING
   Sum(T.id_tag IN ('required1', ... 'requiredn')) = <n>
      -- where n is the required number of tags
   AND Sum(T.id_tag IN ('optional1', ... 'optionaln')) >= 1

不过，我更喜欢这样的结构，因为这样信息只列出一次:

SELECT
   M.id_media
FROM
   media M
   INNER JOIN tag_media T ON M.id_media = T.id_media
   INNER JOIN (
      SELECT 'required1' id_tag, 1 required UNION ALL SELECT 'required2', 1 ...
      UNION ALL SELECT 'optional1', 0 UNION ALL SELECT 'optional2', 0
   ) S ON T.id_tag = S.id_tag
GROUP BY M.id_media
HAVING
   Sum(required) = <n> -- where n is the required number of tags
   AND Sum(1 - required) >= 1

如果您可以在 MySQL 中使用 CTE，那么将 S 派生表转换为 CTE(或将其放入临时表)将使您更改 <n>从所需选项的字面数量到 (SELECT Count(*) from S) .

注意:从技术上讲，这些查询可以重写为完全针对 tag_media 表。但是，如果您想从媒体表中提取其他信息，那么您可能会这样做。