mysql 嵌套查询最大值,嵌套查詢，在表B中查找表A中的最大值 - Nested query to find details in table B for maximum value in tabl...

I've got a huge bunch of flights travelling between airports.

我有一大堆航班往返於機場之間。

Each airport has an ID and (x,y) coordinates.

每個機場都有一個ID和(x,y)坐標。

For a given list of flights belonging to a user, I want to find the northernmost (highest y) airport visited.

對於屬於用戶的給定航班列表，我希望找到所訪問的最北端(最高的y)機場。

Here's the query I'm currently using:

下面是我正在使用的查詢:

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]

)

This works beautifully and reasonably fast as long as y is unique (= there's only one airport at that latitude), but fails once it isn't. Unfortunately this happens quite often, as eg. military and civilian airports have separate entries even though they occupy the same coordinates.

只要y是唯一的(在這個緯度上只有一個機場)，它就會運行得很好，而且運行得相當快，但一旦不是，它就會失敗。不幸的是，這種情況經常發生。軍用機場和民用機場都有各自的入口，盡管它們的坐標相同。

What I'd really want to do is find the airport with MAX(y) in the subquery and return the actual matching airport (a.apid), instead of returning the value of y and then matching it again. Any suggestions?

我真正想做的是在子查詢中找到帶MAX(y)的機場，並返回實際匹配的機場(a.apid)，而不是返回y的值，然后再次匹配它。有什么建議嗎?

Assume the user has only this one flight, from apid '3728':

假設用戶只有一次飛行，從apid '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 |

+------+----------+----------+----------+----------+------+------+-----------+-------+--------+------+------+------+--------+----------+--------------+--------------+---------------------+------+------------+------+

And there are two airports at the same coordinates:

在同一個坐標下有兩個機場:

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 |

+-----------------------+----------+---------------+------+------+-------------+-----------+-----------+------+------+----------+------+

If you run the original query, the subquery will return y=21.318681, which in turn will match either apid 3728 (correct) or apid 7055 (wrong).

如果運行原始查詢，子查詢將返回y=21.318681，它將與apid 3728(正確)或apid 7055(錯誤)匹配。

4 个解决方案

#1

1

How does the following query perform? It works by first finding the northmost Y cordinate in the set of airports visited. Then an identical query is performed which is filtered by the Y coordinate in the previous query. The final step is to find the airport.

下面的查詢是如何執行的?它的工作原理是首先在機場的集合中找到最北的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);

Edit. Alternative query that may be less confusing to the optimizer

編輯。對優化器來說不那么混亂的可選查詢

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);

#2

2

What about this:

這個:

SELECT name,iata,icao,apid,x,y

FROM airports AS a, flights AS f

WHERE f.src_apid=a.apid OR f.dst_apid=a.apid

ORDER BY y DESC LIMIT 1

You take all flights of the concerned users, order them from northern to southern, and take the first one from the list.

你乘坐所有相關用戶的航班，從北部到南部，從列表中選擇第一個。

#3

1

third attempt, using assumed user (userid,name) table

第三次嘗試，使用假設的user (userid,name)表

select u.name, ap.name

, ap.iata

, ap.icao

, ap.apid

, ap.x

, max(ap.y)

from users u

, airports ap

, flights f

where u.userid=f.userid

and ( f.src_apid=ap.apid

OR f.dst_apid=ap.apid

)

group by u.name, ap.name,ap.iata,ap.icao,ap.apid,ap.x

you can now restrict the query to the one user you are interested in ..

您現在可以將查詢限制為您感興趣的一個用戶。

comment on GROUP BY:

評論組:

strictly speaking MySQL would allow me to write that group by as 'group by u.name, ap.name'.

嚴格地說，MySQL允許我把這個組寫成“按u - name、ap.name的組”。

Other SQL dialects don't, they ask that all selected fields that are not aggregated be in the GROUP BY statement.

其他SQL方言則沒有，它們要求未聚合的所有選定字段都在GROUP BY語句中。

So I tend to be 'chicken' when selecting my GROUP BY fields ...

所以我在按領域選擇團隊的時候，往往會顯得很“膽小”……

#4

1

OK, perhaps something like this:

好吧，也許是這樣:

SELECT name, iata, icao, apid, x, y

FROM airports

WHERE y = (SELECT MAX(A.y)

FROM airports AS a

INNER JOIN flights AS f

ON (F.SRC_APID = A.APID OR

F.DST_APID = A.APID)

WHERE f.uid = [user_id]) AND

apid IN (SELECT SRC_APID AS APID

FROM FLIGHTS

WHERE UID = [user_id]

UNION ALL

SELECT DEST_APID AS APID

FROM FLIGHTS

WHERE UID = [user_id])

Can't guarantee how this will perform, but perhaps it's a step in the right direction.

不能保證它將如何運行，但這可能是朝着正確方向邁出的一步。

Share and enjoy.

分享和享受。