检查查询是否包含 oracle 中子查询的所有值 [英] Check that a query contains all the values of a subquery in oracle

问题描述

想象一下，您有一个查询，并且只想显示具有子查询所有值的那个查询.例如，我们有下表:

Imagine that you have a query and you want to only show that one who has ALL the values of a subquery. For example, we have the following table:

CREATE TABLE test
(
code VARCHAR2(4),
year VARCHAR2(4),
action VARCHAR2(50),
CONSTRAINT pk PRIMARY KEY (code, year)
);

以及以下寄存器:

INSERT INTO test
VALUES ('1','2020','Departure');
INSERT INTO test
VALUES ('1','2021','Arrival');

INSERT INTO test
VALUES ('2','2020','Departure');

想象一下子查询返回以下值:

Imagine that a subquery returns me the following values:

('Departure','Arrival')

所以我想做一个查询，只返回与子查询中返回的两个值相匹配的那些代码和年份.查看寄存器，它应该只返回 return ('1','2020') 和 ('1','2021') 因为它们是唯一的操作是 'Arrival' 和 'Departure' 的.我怎么办?

So I want to make a query that returns me only those codes and years which match both of the values that have been returned at the subquery. Looking at the registers, it should return only return ('1','2020') and ('1','2021') because they are the only ones whose actions are 'Arrival' and 'Departure'. How could I do it?

推荐答案

稍微扩展了示例数据，其中CODEs 1 和 3 都有 Arrival 和 Departure:

With a little bit expanded sample data, where CODEs 1 and 3 have both Arrival and Departure:

SQL> with test (code, year, action) as
  2    (select 1, 2020, 'Departure' from dual union all
  3     select 1, 2021, 'Arrival'   from dual union all
  4     select 2, 2020, 'Departure' from dual union all
  5     --
  6     select 3, 2018, 'Arrival'   from dual union all
  7     select 3, 2019, 'Departure' from dual
  8    ),
  9  subq as
 10    (select distinct action,
 11            count(distinct action) over () cnt_da
 12     from test
 13    )
 14  select a.code, a.year
 15  from test a join subq s on a.action = s.action
 16  where s.cnt_da = (select count(distinct action)
 17                    from test b
 18                    where b.code = a.code
 19                   );

      CODE       YEAR
---------- ----------
         1       2021
         1       2020
         3       2019
         3       2018

SQL>

<小时>

另一种选择，使用 MINUS 集合运算符:

SQL> with test (code, year, action) as
  2    (select 1, 2020, 'Departure' from dual union all
  3     select 1, 2021, 'Arrival'   from dual union all
  4     select 2, 2020, 'Departure' from dual union all
  5     --
  6     select 3, 2018, 'Arrival'   from dual union all
  7     select 3, 2019, 'Departure' from dual
  8    ),
  9  subq as
 10    (select distinct action from test)       --> this is your "subquery"
 11  select code, year
 12  from test a
 13  where (select s.action from subq s
 14         minus
 15         select b.action from test b where b.code = a.code
 16        ) is null;

      CODE       YEAR
---------- ----------
         1       2020
         1       2021
         3       2018
         3       2019

SQL>

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