卡在“在条款”
我有如下表:卡在“在条款”
create table passenger (passenger_ID varchar (10) , passenger_name varchar (30) , passenger_city varchar (30) , primary key (passenger_ID)) ;
create table flight (flight_number varchar (10) , departure_airport varchar (10), arrival_airport varchar (10) , primary key (flight_number));
create table seat (
flight_number varchar (10),
seat_number varchar (10),
primary key(flight_number, seat_number),
foreign key(flight_number) references flight);
create table reservation(
passenger_ID varchar (10)
flight_number varchar (10) , seat_number varchar (10) ,
day date ,
fare numeric(8,2),
primary key (flight_number, seat_number,day),
foreign key (flight_number, seat_number) references seat) ,
foreign key (passenger_ID) references passenger ;
我被困在了这个问题: 查找保留一个航班每位乘客的ID(或航班)从“ALB”出发,从未预订到达'ALB'的航班。对于此查询,请使用in子句或not in子句。
我写了这么多查询:
select a.passenger_id from
reservation a
where a.flight_number in (select b.flight_number from flight b where
b.departure_airport = 'ALB'
and b.arrival_airport <> 'ALB');
但这种查询是不正确的。子查询返回与过滤条件相匹配的所有航班号。但是主要的select查询会返回子查询返回的flight_number的所有乘客id,即使该乘客的arrival_airport是ALB。
我的预约表:
passenger_id flight_number seat_number day fare
2 A2 201 10/1/17 1083
1 A3 301 10/1/17 1173.25
1 A4 402 10/1/17 846.81
2 A5 501 10/1/17 752.72
1 A5 502 10/1/17 485
4 A5 506 10/1/17 970
2 A6 601 10/1/17 388
1 A7 703 10/1/17 921.5
3 A7 704 10/1/17 921.5
2 A8 804 10/1/17 970
4 A8 805 10/1/17 970
我的飞行表:
flight_number departure_airport arrival_airport
A1 Ktm Pkr
A2 Ktm NY
A3 Ktm Ind
A4 Ktm Chn
A5 ALB KTM
A6 ALB PKR
A7 KTM ALB
A8 PKR ALB
想:两个条件。所以从你的思路来看:
select r.passenger_id
from reservation r
where r.flight_number in (select f.flight_number
from flight f
where f.departure_airport = 'ALB'
) and
r.flight_number not in (select f.flight_number
from flight f
where f.arrival_airport = 'ALB'
);
但是,那不行。任何到达ALB的航班都将从其他机场出发。所以,这只是选择所有预订ALB航班的乘客。此逻辑在航班级别,而不是乘客级别。
嗯。您实际上需要的信息是所有航班客户“一次”。这表明聚合:
select r.passenger_id
from reservation r join
flight f
on r.flight_number = f.flight_number
group by r.passenger_id
having sum(case when f.departure_airport = 'ALB' then 1 else 0 end) > 0 and -- reserved a flight from ALB
sum(case when f.arrival_airport = 'ALB' then 1 else 0 end) = 0; -- never reserved a flight to ALB
只是为了以稍微不同的方式证明@ GordonLinoff的答案的正确性,让我们稍微区别看待它。 :-)(See this SQLFiddle)。 (请注意,我填充了PASSENGER表,我们稍后会使用它。:-)。首先,让我们抓住所有谁已预留至少一个航班从ALB离开的乘客:
-- Find passengers who have reserved at least one flight departing from ALB
SELECT DISTINCT r.PASSENGER_ID
FROM RESERVATION r
INNER JOIN FLIGHT f
ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
WHERE f.DEPARTURE_AIRPORT = 'ALB';
这将返回PASSENGER_ID的1,2,4
接下来,让我们找到所有谁在保留乘客至少一个飞行到达ALB:
-- Find passengers who have reserved at least one flight arriving at ALB
SELECT DISTINCT r.PASSENGER_ID
FROM RESERVATION r
INNER JOIN FLIGHT f
ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
WHERE f.ARRIVAL_AIRPORT = 'ALB';
这返回PASSENGER_ID的1,2,3和4。
上述结果的琐碎审查表明显然,没有任何乘客预订了ALB航班,并且没有预订到达ALB的航班。但是因为我们只想让查询向我们展示我们想要什么,而不必去思考(毕竟,这是计算机应该做的事情:-)我们将把上述查询放在一起:
-- Now put them together
SELECT p.PASSENGER_ID
FROM PASSENGER p
INNER JOIN (SELECT DISTINCT r.PASSENGER_ID
FROM RESERVATION r
INNER JOIN FLIGHT f
ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
WHERE f.DEPARTURE_AIRPORT = 'ALB') d
ON d.PASSENGER_ID = p.PASSENGER_ID
LEFT OUTER JOIN (SELECT DISTINCT r.PASSENGER_ID
FROM RESERVATION r
INNER JOIN FLIGHT f
ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
WHERE f.ARRIVAL_AIRPORT = 'ALB') a
ON a.PASSENGER_ID = p.PASSENGER_ID
WHERE a.PASSENGER_ID IS NULL;
如预期的那样,此查询不返回任何结果。
但...我们真的想确保我们的查询真的有效。 Soooo ...让我们添加几行到几个表。首先,我们将添加一个新的乘客:
insert into passenger (passenger_ID, passenger_name, passenger_city)
values ('5', 'Bugs Bunny', 'ABC');
,我们还会增加对兔子先生,一个在那里,他离开ALB航班:
INSERT INTO RESERVATION (PASSENGER_ID, FLIGHT_NUMBER, SEAT_NUMBER, DAY, FARE)
VALUES ('5', 'A6', '123', TO_DATE('2017-10-01','YYYY-MM-DD'), 400);
所以现在我们有乘员5,先生B. Bunny,离开ALB--但他从未飞入ALB。 (我认为那是因为他应该在阿尔伯克基左转弯:-)。所以我们的查询应该返回乘客5,并且if you go look at this SQLFiddle你会看到发生的情况。
Ehhh - 祝你好运,doc。
集中在乘客的替代航班,使用IN()
和NOT IN()
SELECT DISTINCT
r.passenger_id
FROM reservation r
WHERE r.passenger_id IN (
SELECT
r.passenger_id
FROM RESERVATION r
INNER JOIN FLIGHT f ON f.flight_number = r.flight_number
WHERE f.DEPARTURE_AIRPORT = 'ALB'
)
AND r.passenger_id NOT IN (
SELECT
r.passenger_id
FROM RESERVATION r
INNER JOIN FLIGHT f ON f.flight_number = r.flight_number
WHERE f.ARRIVAL_AIRPORT = 'ALB'
)
;
但是鲍勃贾维斯分析也表明,在您的样本数据中没有行符合分配条件。
:这个问题也可以使用SET OPERATIONS来解决吗? –
不确定你的意思。我个人很少使用IN(),除非它是一个固定的值列表。您可以轻松地将上述查询转换为使用EXISTS&NOT EXISTS,并且Gordon Linoff显示了HAVING的用法。 –
哦,Bob Jarvis也展示了如何使用联合派生表。所以你有很多可以从恕我直言选择 –
对不起,它没有工作。请看我的桌子。只有A5和A6航班从ALB出发。所以,乘客ID 1,2和4是符合条件的。但passenger_id 4已预约到达ALB,因此应予以忽略。所以我应该只得到1和2乘客ID –
@SujanShrestha。 。 。你使用了“GROUP BY”查询吗?也许你应该设置一个SQL小提琴或Rextester。 –
http://sqlfiddle.com/#!17/deeeb/1 –