SQL - 标签搜索查询 [英] SQL - Tags searching query

问题描述

我的数据库中有下表:

ID  name
1   x
2   x
3   y
1   y
1   z

现在我只想选择具有x"和y"值的标记名称的对象(ID).在这种情况下，这将仅是 ID = 1 的记录，因为搜索值集('x' 和 'y')是该记录可能名称集('x'、'y' 和 'z')的子集.

Now I want to select only this objects (ID's) which has both 'x' and 'y' value s tag name. In this case this will be only record with ID = 1 because sought values set ('x' and 'y') is subset of this record possible names set ('x', 'y' and 'z').

如何编写 SQL 查询?
感谢帮助:)

How to write a SQL query?
Thanks for help :)

推荐答案

一种方法使用聚合:

select id
from t
where name in ('x', 'y')
group by id
having count(*) = 2;

如果您关心性能，您可能想将其与:

If you care about performance you might want to compare this to:

select id
from t tx join
     t ty
     on tx.id = ty.id and tx.name = 'x' and ty.name = 'y';

第一个版本更容易推广到更多标签.在某些情况下，第二个可能具有更好的性能.

The first version is easier to generalize to more tags. Under some circumstances, the second might have better performance.

这篇关于SQL - 标签搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！