select ct.name "起飞城市",ct.name "降落城市",to_char(fg.s_time, 'yyyy-mm-dd hh24:mi:ss') "起飞时间",to_char(fg.e_time, 'yyyy-mm-dd hh24:mi:ss') "降落时间" from flight fg,city ct where fg.s_city_id=ct.id and fg.e_city_id=ct.id order by fg.s_time desc;
mysql> select c.name,c2.name,f.s_time,f.e_time from flight f , city c ,city c2 w here f.s_city_id=c2.id and f.e_city_id=c.id and f.s_city_id=1 and f.e_city_id = 2 order by f.s_time desc ; 输出结果是: +----------+---------+------------+------------+ | name | name | s_time | e_time | +----------+---------+------------+------------+ | shanghai | beijing | 2011-11-03 | 2011-11-02 | | shanghai | beijing | 2011-11-02 | 2011-11-02 | | shanghai | beijing | 2011-11-01 | 2011-11-02 | | shanghai | beijing | 2011-10-11 | 2011-11-02 | | shanghai | beijing | 2011-10-03 | 2011-11-02 | +----------+---------+------------+------------+其中北京城市的id是1 上海城市的id是2。 我也只是当场试试的,以前也没有写过这样的sql,所以如果有什么效率不好的问题,请恕罪。
行了,给分吧 select t.s_city_name, t.e_city_name, t.s_time, t.e_time from (select a.s_time, a.e_time, (select b.name from city b where b.id = a.s_city_id) s_city_name, (select b.name from city b where b.id = a.e_city_id) e_city_name from flight a) t where t.s_city_name = '北京' and t.e_city_name = '上海';
SELECT A.NAME, B.NAME , A.S_TIME, A.E_TIME FROM ( SELECT F1.ID, C1.NAME , F1.S_TIME, F1.E_TIME FROM FLIGHT F1 JOIN CITY C1 ON F1.S_CITY_ID = C1.ID AND C1.NAME = '北京' ) A
JOIN ( SELECT F2.ID, C2.NAME FROM FLIGHT F2 JOIN CITY C2 ON F2.E_CITY_ID = C2.ID AND C2.NAME = '上海' ) B
ON A.ID = B.ID;
select (select city.name from city where city.id = flight.`s_city_id`) as sCity, (select city.name from city where city.id = flight.`e_city_id`) as eCity, flight.`s_time`, flight.`e_time` from flight order by flight.`s_time`
select c.name,c2.name,f.s_time,f.e_time from flight f , city c ,city c2 where f.s_city_id=c.id and f.e_city_id=c2.id and c.name="北京" and c2.name="上海" order by f.s_time desc ;
select ct.name "起飞城市",ct.name "降落城市",to_char(fg.s_time, 'yyyy-mm-dd
hh24:mi:ss') "起飞时间",to_char(fg.e_time, 'yyyy-mm-dd hh24:mi:ss') "降落时间"
from flight fg,city ct where fg.s_city_id=ct.id and fg.e_city_id=ct.id
order by fg.s_time desc;
here f.s_city_id=c2.id and f.e_city_id=c.id and f.s_city_id=1 and f.e_city_id =
2 order by f.s_time desc ;
输出结果是:
+----------+---------+------------+------------+
| name | name | s_time | e_time |
+----------+---------+------------+------------+
| shanghai | beijing | 2011-11-03 | 2011-11-02 |
| shanghai | beijing | 2011-11-02 | 2011-11-02 |
| shanghai | beijing | 2011-11-01 | 2011-11-02 |
| shanghai | beijing | 2011-10-11 | 2011-11-02 |
| shanghai | beijing | 2011-10-03 | 2011-11-02 |
+----------+---------+------------+------------+其中北京城市的id是1 上海城市的id是2。
我也只是当场试试的,以前也没有写过这样的sql,所以如果有什么效率不好的问题,请恕罪。
select t.s_city_name, t.e_city_name, t.s_time, t.e_time
from (select a.s_time,
a.e_time,
(select b.name from city b where b.id = a.s_city_id) s_city_name,
(select b.name from city b where b.id = a.e_city_id) e_city_name
from flight a) t
where t.s_city_name = '北京'
and t.e_city_name = '上海';
SELECT
A.NAME, B.NAME , A.S_TIME, A.E_TIME
FROM
(
SELECT
F1.ID, C1.NAME , F1.S_TIME, F1.E_TIME
FROM FLIGHT F1 JOIN CITY C1 ON F1.S_CITY_ID = C1.ID AND C1.NAME = '北京'
) A
JOIN
(
SELECT
F2.ID, C2.NAME
FROM FLIGHT F2 JOIN CITY C2 ON F2.E_CITY_ID = C2.ID AND C2.NAME = '上海'
) B
ON A.ID = B.ID;
(select city.name from city
where city.id = flight.`s_city_id`) as sCity,
(select city.name from city
where city.id = flight.`e_city_id`) as eCity,
flight.`s_time`, flight.`e_time` from flight order by flight.`s_time`