在Oracle中查询以使用子查询进行选择 [英] Query in Oracle to select with subquery

问题描述

我的Oracle数据库上有下表:

I have the following table on my Oracle database:

CREATE TABLE test
(
 flight NUMBER(4),
 date   DATE,
 action VARCHAR2(50),
 CONSTRAINT pk PRIMARY KEY (flight,date)
);

和以下寄存器:

BEGIN
 INSERT INTO test VALUES ('1234', '2020-02-29 18:00', 'Departure');
 INSERT INTO test VALUES ('1234', '2020-02-29 19:00', 'Arrival');

 INSERT INTO test VALUES ('4321', '2020-02-20 22:00', 'Departure');
 INSERT INTO test VALUES ('4321', '2020-02-21 04:30', 'Arrival');
END;
/

我的问题是，我想进行一次选择，以便仅在航班起飞并于同一天到达时才将航班号返回给我.例如，航班"1234"已在第29天起飞并在第29天到达，但另一方面，航班"4321"已在第20天离开并在第21天到达.我只想选择航班"1234"'，因为它是唯一满足要求的人.

My problem is that I want to make a SELECT that returns me the flight number only when it has departed and arrived at the same day. For example, flight '1234' has departed on day 29 and arrived on day 29, but in the other hand, the flight '4321' has departed on day 20 and arrived on day 21. I only would like to select the flight '1234', because it is the only one who meets the requeriments.

我一直在考虑以下选择，但是它不起作用，因为子查询返回的不只是一个值:

I have been thinking about the following select, but it doesn't work because the subquery returns more than just one value:

SELECT flight
  FROM test
 WHERE action = 'Departure'
   AND TO_CHAR(date, 'YYYY-MM-DD') = (SELECT TO_CHAR(date, 'YYYY-MM-DD')
                                        FROM test
                                       WHERE action = 'Arrival');

非常感谢您.

推荐答案

我们不知道数据模型中的航班号是否唯一代表航班.我们还不知道除出发"和到达"以外是否还有其他动作.

We don't know, whether a flight number in your data model uniquely represents a flight or not. We also don't know whether there can be any actions apart from 'Departure' and 'Arrival'.

以下解决方案将航班号与航班日期结合在一起，并考虑了有问题的两个操作，因此它们在任何情况下都有效:

The following solutions look at a flight number in combination with the flight date and consider the two actions in question, so they work in any case:

select flight, trunc(date) from test where action = 'Departure'
intersect
select flight, trunc(date) from test where action = 'Arrival';

具有聚合:

select flight, trunc(date)
from test
group by flight, trunc(date)
having count(case when action = 'Departure' then 1 end) = 1
   and count(case when action = 'Arrival' then 1 end) = 1;

这篇关于在Oracle中查询以使用子查询进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！