一张航空表a，字段:航班id，起始城市id，目的城市id。
一张城市表b，字段:城市id，城市名称。
怎样查出从北京到上海的航班id?
select a.id from a inner join b as startcity on a.start_cityid=startcity.id inner join b as endcity on a.end_cityid=endcity.id where startcity.name='北京' and endcity.name='上海';
这样？
select a.id from a where a.start_city='北京' and a.end_city='上海';
如果我没理解错。 这个查询跟 b 表没直接关系。
select id from a where startCityId = (select id from b where cityName='北京') and endCityId = (select id from b where cityName='上海');
[code="sql"]
SELECT a.flight_id
FROM a
LEFT JOIN b AS m
ON a.start_city_id = m.city_id
LEFT JOIN b AS n
ON a.dest_city_id = n.city_id
WHERE m.city_name = '北京'
AND n.city_name = '上海';
[/code]
哦，是可以像第2个答案用inner join的
[code="sql"]
SELECT a.flight_id
FROM a,
b AS m,
b AS n
WHERE a.start_city_id = m.city_id
AND a.dest_city_id = n.city_id
AND m.city_name = '北京'
AND n.city_name = '上海';
[/code]