MySQL-选择与所有标签匹配的ID [英] Mysql - select ids that match all tags

问题描述

我有一个交叉引用表:

ID | tag
1  | 4
1  | 5
1  | 6
2  | 4
2  | 5
2  | 8
3  | 2

我需要选择与一组标签的 all 相匹配的ID.例如，如果给我标签'4','5'，我将获得ID '1','2'.如果给我标签'4','2'，我将不会获得任何ID，因为没有与标签 all 匹配的ID.

I need to select the IDs that match all of a set of tags. For example, if I were given the tags '4','5' I would get IDs '1','2'. If I were given the tags '4','2' I would not get any IDs because there were no IDs that matched all of the tags.

此外，如果为我提供了标签'4','9'，那么我也不应获取任何结果ID，因为对'9'的搜索将产生NULL值，因此没有ID与 all匹配标签.

Also, if I were given the tags '4','9' then I also should not get any resulting IDs because a search for '9' would result in a NULL value, and therefore no IDs match all the tags.

最近两天我一直在拔头发.希望有人可以帮助我.

I've been pulling my hair out for the last 2 days. Hopefully someone can help me.

推荐答案

查询的思想是，您需要将记录数与WHERE子句中提供的值数进行匹配.

The idea of the query is that you need to match the number of records to the number of values you have provided in the WHERE clause.

SELECT ID
FROM tableName
WHERE tag IN (4, 8)
GROUP BY ID
HAVING COUNT(*) = 2

• SQLFiddle演示
• SQLFiddle Demo

如果未在标签上为每个ID指定唯一约束，则需要DISTINCT

if unique constraint was not specified on tag for every ID, then DISTINCT is needed

SELECT ID
FROM tableName
WHERE tag IN (4, 8)
GROUP BY ID
HAVING COUNT(DISTINCT tag) = 2

这篇关于MySQL-选择与所有标签匹配的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！