SQL查询基于另一列中的唯一元素在列中查找公共ID [英] SQL Query Finding a common id in a column based on unique elements in another column

问题描述

我一直在努力尝试找到一个相对简单的方法来执行此查询。我尝试了连接和连接，甚至尝试执行查询外的逻辑，但它变得非常复杂。

这是我的SQL：

  AttributeName_Id | ProductPrice_Id 
 9 | 1 
 4 | 1 
 9 | 2 
 5 | 2 
 9 | 3 
 6 | 3 
 9 | 4 
 7 | 4 
  

我有输入9和5.我如何执行查询， ？

解决方案

这是一个set-within-sets查询。我喜欢使用 group by 和拥有解决这些问题，因为它非常灵活：

> 从表t中选择ProductPrice_Id

其中AttributeName_Id在（9，5）
group by ProductPrice_Id
count（*）= 2;


这假定表没有重复项;否则，您可能需要 count（distinct）。

I've been struggling to try to find a relatively simple way to perform this query. I have tried joins and wheres, and even tried to perform the logic outside of the query, but it gets very complicated.

This is my SQL:

AttributeName_Id     |     ProductPrice_Id
    9                    |     1
    4                    |     1
    9                    |     2
    5                    |     2
    9                    |     3
    6                    |     3
    9                    |     4
    7                    |     4

I have inputs 9 and 5. How would I perform a query so I can retrieve the value 2?

解决方案

This is a "set-within-sets" query. I like to solve these using group by and having, because it is very flexible:

select ProductPrice_Id
from table t
where AttributeName_Id in (9, 5)
group by ProductPrice_Id
having count(*) = 2;

This assumes that the table has no duplicates; otherwise, you might want count(distinct).

这篇关于SQL查询基于另一列中的唯一元素在列中查找公共ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！