mysql - 并搜索 MySQL 多对多表

Question

我有下面的表格，我想对其进行搜索:

Table A
+----+----------------------------+
| ID | description                |
+----+----------------------------+
|  0 | horse going bad            |
|  1 | Older Years of Resolutions |
|  2 | The knockknock pirate      |
|  3 | The Wish list              |
|  4 | list that's no list        |
+----+----------------------------+

table TAGS
+----+------------+
| ID | tag        |
+----+------------+
|  0 | list       |
|  1 | knockknock |
+----+------------+

table TAGLINKS
+-------+--------+
| TAGID | JOKEID |
+-------+--------+
|   0   |    2   |
|   0   |    3   |
+-------+--------+

当我进行搜索时:

select A.* from tags
   join taglinks on tagid=tags.id
   join A on A.id=jokeid
where tag in ('list','knockknock') 

为我提供 A 中所有在其标签(或两者)中具有“list”或“knockknock”的条目 (2,3)。我正在寻找的是从表 A 中获取条目的查询，这些条目连接到 BOTH list 和 knockknock 标签(仅限 2)。

我还将这些数据与表 A 的描述中的直接搜索结合起来，这是需要考虑的事情..

现在我有:

select A.* from tags
   join taglinks on tagid=tags.id
   join A on A.id=jokeid
where tag in ('list','knockknock') 

UNION

select * from A where locate('list',description) and locate('knockknock',description)

但我也从表A中得到3个，我只想要2个

Answer 1

要确保所有标签都有 'list', 'knockknock'，您应该添加 GROUP BY 子句和 HAVING:

...
WHERE tag in ('list', 'knockknock')
GROUP BY tag
HAVING COUNT(tag) = 2)

类似于:

SELECT A.* 
FROM tags
INNER JOIN taglinks ON tagid = tags.id
INNER JOIN A on A.id=jokeid
WHERE id IN (SELECT id
             FROM tags
             WHERE tag in ('list', 'knockknock')
             GROUP BY tag
             HAVING COUNT(tag) = 2)