MYSQL:从多个值与单个列匹配至少两次的表中选择 id

Question

我有一个看起来像这样的表:

+----+--------------------------------+
| id | slug                           |
+----+--------------------------------+
|  1 | gift                           |
|  1 | psychological-manipulation     |
|  1 | christmas                      |
|  1 | giving                         |
|  1 | the-town-santa-forgot          |
|  1 | santa-claus                    |
|  1 | mp3                            |
|  1 | christmas                      |
|  2 | entertainment-culture          |
|  2 | christmas                      |
|  2 | culture                        |
|  2 | literature                     |
|  2 | christmas-music                |
|  2 | christmas-window               |
|  2 | broadcasting-nec               |
|  2 | how-the-grinch-stole-christmas |
|  2 | the-polar-express              |
|  2 | banker                         |
|  2 | christmas                      |
|  2 | potter                         |
|  2 | christmas-eve                  |
|  2 | bailey                         |
|  2 | its-a-wonderful-life           |
|  2 | the-polar-express              |
|  2 | disney                         |
|  2 | tim-burton                     |
|  2 | a-christmas-carol              |
|  2 | the-nightmare-before-christmas |
|  2 | chuck-jones                    |
+----+--------------------------------+

我想从表中获取唯一 ID，其中至少有两个 slug 列表与给定 ID 匹配。

例如，假设我有 slugs 值:

• 礼物
• 圣诞节
• 给予

我想要所有具有至少 2 个匹配记录的唯一 ID。

即只有一个 id 既有礼物又有圣诞节鼻涕虫，或者有礼物和圣诞节鼻涕虫，或者有礼物和给予鼻涕虫，等等......

Answer 1

您可以使用 distinct 修饰符来计算每个 ID 的不同 slug 的数量:

SELECT   id
FROM     mytable
WHERE    slug IN ('gift', 'christmass', 'giving')
GROUP BY id
HAVING   COUNT(DISTINCT slug) >= 2