SQL where 限制

Question

下面是我的输出，我只想显示那些重复的名字。每个名字都是飞行员，数字是飞行员驾驶的飞机类型。我想显示驾驶不止一架飞机的飞行员的姓名。我正在使用 sql*plus

PIL_PILOTNAME                  DEP_EQUIP_NO 
------------------------------ ------------ 
Gladchuk, Chet                 1085
Gladchuk, Chet                 1345
Green, Edward L.               1489
Harris, Kenneth C.             1253
Leiss, Ernst L.                1194
Long, Stuart                   1081
Pasewark, William B.           1026
Pasewark, William B.           1347
Scamell, Richard               1368

我的输出应该只显示:

PIL_PILOTNAME                  DEP_EQUIP_NO 
------------------------------ ------------ 
Gladchuk, Chet                 1085
Gladchuk, Chet                 1345
Pasewark, William B.           1026
Pasewark, William B.           1347

这是我的查询:

select distinct pil_pilotname,dep_equip_no from pilots  join departures
on dep_pilot_id = pil_pilot_id  join equip_type
on eq_equip_no = dep_equip_no
order by pil_pilotname;

Answer 1

在这种情况下，最好的选择是使用分析

select PIL_PILOTNAME, DEP_EQUIP_NO
from (
        select pil_pilotname,dep_equip_no, count(*) over (partition by pil_pilot_id) as cnt
        from pilots
            join departures on (dep_pilot_id = pil_pilot_id)
            join equip_type on (eq_equip_no = dep_equip_no)
    )
where cnt > 1
order by pil_pilotname;