SQL检查列中的所有值 [英] SQL to check for all values in column

问题描述

我在Oracle DB中有下表.

I have the following table in Oracle DB.

ID  VALUE
-----------
1   1  
1   2  
1   3   
2   1   
2   2  
3   1  
3   2  
3   3  
4   1

如何选择具有所有3个值(1,2,3)的ID

How can I select ID's which have all 3 values (1,2,3)

推荐答案

最简单的选项通常是这样的

The simplest option is generally something like this

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select 1 id, 1 val from dual union all
  3    select 1 id, 2 val from dual union all
  4    select 1 id, 3 val from dual union all
  5    select 2 id, 1 val from dual union all
  6    select 2 id, 2 val from dual union all
  7    select 3 id, 1 val from dual union all
  8    select 3 id, 2 val from dual union all
  9    select 3 id, 3 val from dual union all
 10    select 4 id, 1 val from dual
 11  )
 12  select id
 13    from x
 14   where val in (1,2,3)
 15   group by id
 16* having count(distinct val) = 3
SQL> /

        ID
----------
         1
         3

WHERE子句标识您感兴趣的值.HAVING子句告诉您其中需要存在多少个这些值.例如，如果您希望所有具有至少3个值中的2个的行，则可以更改HAVING子句以查找2的COUNT.

The WHERE clause identifies the values you're interested in. The HAVING clause tells you how many of those values need to exist. If you wanted all the rows that had at least 2 of the 3 values, for example, you'd change the HAVING clause to look for a COUNT of 2.

如果保证每个id特定的val最多出现一次，则可以在HAVING子句中消除distinct.

If a particular val is guaranteed to occur at most once per id, you can eliminate the distinct in the HAVING clause.

这篇关于SQL检查列中的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！