我在机场之间有很多航班.

每个机场都有一个ID和(x,y)坐标.

对于属于用户的给定航班列表,我想找到所访问的最北(y最高)机场.

这是我当前正在使用的查询：

SELECT name,iata,icao,apid,x,y 
  FROM airports 
 WHERE y=(SELECT MAX(y) 
            FROM airports AS a
               , flights AS f 
           WHERE (f.src_apid=a.apid OR f.dst_apid=a.apid) AND f.uid=[user_id]
         )

只要y是唯一的(=在那个纬度上只有一个机场),这可以很好地工作并且相当快,但是一旦y不唯一,它就会失败.不幸的是,这种情况经常发生,例如.军事和民用机场即使占据相同的坐标,也具有单独的条目.

我真正想做的是在子查询中找到具有MAX(y)的机场,并返回实际匹配的机场(a.apid),而不是返回y的值然后再次进行匹配.有什么建议么？

假设用户只有一次航班,航班号为“ 3728”：

mysql> select * from flights where uid=35 and src_apid=3728 limit 1;
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+
| uid  | src_apid | src_time | dst_apid | distance | code | seat | seat_type | class | reason | plid | alid | trid | fid    | duration | registration | note         | upd_time            | opp  | src_date   | mode |
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+
|   35 |     3728 | NULL     |     3992 |     4116 | NW16 | 23C  | A         | Y     | L      |  167 | 3731 | NULL | 107493 | 08:00:00 |              | del. typhoon | 2008-10-04 10:40:58 | Y    | 2001-08-22 | F    | 
+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+

并且有两个处于相同坐标的机场：

mysql> select * from airports where y=21.318681;
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+
| name                  | city     | country       | iata | icao | x           | y         | elevation | apid | uid  | timezone | dst  |
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+
| Honolulu Intl         | Honolulu | United States | HNL  | PHNL | -157.922428 | 21.318681 |        13 | 3728 | NULL |      -10 | N    | 
| Hickam Air Force Base | Honolulu | United States |      | PHIK | -157.922428 | 21.318681 |        13 | 7055 |    3 |      -10 | N    | 
+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+

如果运行原始查询,则子查询将返回y = 21.318681,这将匹配apid 3728(正确)或apid 7055(错误).

解决方法:

以下查询如何执行？
它的工作原理是首先找到所访问的机场集合中最北的Y坐标.然后执行相同的查询,该查询由上一个查询中的Y坐标过滤.最后一步是找到机场.

drop table airports;
drop table flights;

create table airports(
   apid    int         not null
  ,apname  varchar(50) not null
  ,x       int         not null
  ,y       int         not null
  ,primary key(apid)
  ,unique(apname)
);

create table flights(
   flight_id int         not null auto_increment
  ,src_apid  int         not null
  ,dst_apid  int         not null
  ,user_id   varchar(20) not null
  ,foreign key(src_apid) references airports(apid)
  ,foreign key(dst_apid) references airports(apid)
  ,primary key(flight_id)
  ,index(user_id)
);

insert into airports(apid, apname, x, y) values(1, 'Northpole Civilian',     50, 100);
insert into airports(apid, apname, x, y) values(2, 'Northpole Military',     50, 100);
insert into airports(apid, apname, x, y) values(3, 'Transit point',          50, 50);
insert into airports(apid, apname, x, y) values(4, 'Southpole Civilian',     50, 0);
insert into airports(apid, apname, x, y) values(5, 'Southpole Military',     50, 0);

insert into flights(src_apid, dst_apid, user_id) values(4, 3, 'Family guy');
insert into flights(src_apid, dst_apid, user_id) values(3, 1, 'Family guy');

insert into flights(src_apid, dst_apid, user_id) values(5, 3, 'Mr Bazooka');
insert into flights(src_apid, dst_apid, user_id) values(3, 2, 'Mr Bazooka');

select airports.apid
      ,airports.apname
      ,airports.x
      ,airports.y
  from (select max(a.y) as y
          from flights  f
          join airports a on (a.apid = f.src_apid or a.apid = f.dst_apid)
         where f.user_id = 'Family guy'
       ) as northmost 
  join (select a.apid
              ,a.y
          from flights  f
          join airports a on (a.apid = f.src_apid or a.apid = f.dst_apid)
         where f.user_id = 'Family guy'
       ) as userflights on(northmost.y = userflights.y)   
  join airports on(userflights.apid = airports.apid);

编辑.可能使优化程序不太混乱的替代查询

select airports.*
  from (select case when s.y > d.y then s.apid else d.apid end as apid
              ,case when s.y > d.y then s.y    else d.y    end as northmost
          from flights  f
          join airports s on(f.src_apid = s.apid)
          join airports d on(f.dst_apid = d.apid)
         where f.user_id = 'Family guy'
         order by northmost desc
         limit 1
       ) as user_flights
  join airports on(airports.apid = user_flights.apid);