表:fly
ID
begID 起始城市ID(对应city表ID),
arrID 到达城市ID(对应city表ID),
startTime 起飞时间,
表:city
ID
CityName 城市名称
求一SQL语句:
(1)求从北京出发的所有航班信息,并按到达城市名称排序.
(2)求从北京出发到达上海的所有航班信息,并按到达城市名称排序
ID
begID 起始城市ID(对应city表ID),
arrID 到达城市ID(对应city表ID),
startTime 起飞时间,
表:city
ID
CityName 城市名称
求一SQL语句:
(1)求从北京出发的所有航班信息,并按到达城市名称排序.
(2)求从北京出发到达上海的所有航班信息,并按到达城市名称排序
SELECT f.id,f.begID,f.arrID,f.startTime
FROM fly f
INNER JOIN city c
ON c.id=f.begID
INNER JOIN city c1
ON c1.id=f.arrID
WHERE c.CityName='北京'
ORDER BY c1.CityName2,
SELECT f.id,f.begID,f.arrID,f.startTime
FROM fly f
INNER JOIN city c
ON c.id=f.begID
INNER JOIN city c1
ON c1.id=f.arrID
WHERE c.CityName='北京' AND c1.CityName='上海'
第二个题,到达地都是上海,还怎么按上海排序,有点看不懂未经测试,仅供参考
select t2.cityname,t3.cityname,t1.starttime
from fly t1 left join city t2 on t1.begid = t2.id
left join city t3 on t1.arrid = t3.id
where t2.cityname = '北京'
order by t3.cityname
(2)
select t2.cityname,t3.cityname,t1.starttime
from fly t1 left join city t2 on t1.begid = t2.id
left join city t3 on t1.arrid = t3.id
where t2.cityname = '北京' and t3.cityname = '上海'
order by t3.cityname
不过2中的排序没有意义啊,只有一个城市上海阿
select a.id,a.beginID,b.CityName,a.arrid,starTime
from fly a,city b
where a.beginID=b.id and b.CityName='北京'
order by b.CityName
select a.id,a.beginID,b.CityName,a.arrid,b.CityName,starTime
from fly a,city b
where a.beginID=b.id and b.CityName='北京'
and a.arrid=b.id and b.CityName='上海'
order by b.CityName
1.
select
(select cityname from city where begID.id = fly.id ) as begcity,
(select cityname from city where arrID.id = fly.id ) as arrcity,
startTime
FROM CITY WHERE begid = (select id from city where name = '北京')order by arrcity2
select
(select cityname from city where begID.id = fly.id ) as begcity,
(select cityname from city where arrID.id = fly.id ) as arrcity,
startTime
FROM CITY WHERE begid = (select id from city where name = '北京') and arrid = (select id from city where name = '上海')order by arrcity