MySql查询关系数据库中的视图
我有一个问题为视图构建MySQL查询。以下是我的关系数据库结构和一些示例数据。MySql查询关系数据库中的视图
travel
| ID | NUM |
| 01 | N01 |
| 02 | N02 |
travel_has_flight
| travel_ID | flight_ID |
| 01 | 01 |
| 01 | 02 |
| 02 | 03 |
flight
| ID | NUM | DEPARTURE_DATE | ARRIVAL_DATE |
| 01 | A01 | 2010-10-10 12:50 | 2010-10-10 17:50 |
| 02 | A02 | 2010-10-17 14:50 | 2010-10-17 14:50 |
| 03 | A03 | 2010-10-22 12:50 | 2010-10-22 17:50 |
预期输出:
从查询作为视图。
| travel_ID | travel_NUM | flight_NUM1 | flight_DEPARTURE_DATE | flight_NUM2 | flight_ARRIVAL_DATE |
| 01 | N01 | A01 | 2010-10-10 12:50 | A02 | 2010-10-17 14:50 |
| 02 | N02 | A03 | 2010-10-10 12:50 | A03 | 2010-10-17 14:50 |
输出应该如下。旅行可以包含1班航班。在这种情况下,输出是来自此次航班的行程+日期的数据。如果旅行包含2班航班,则应返回旅行+第一班航班的出站日期和第二班航班的入境日期的数据。
我会很乐意为此提供任何帮助。
###更新###
我与SQL低于目前卡住了,问题是现在如何单独flight_NUM的加入...
SELECT t.ID, t.NUM, Min(f.DEPARTURE_DATE), MAX(f.ARRIVAL_DATE)
FROM travel t
INNER JOIN travel_has_flight tf ON tf.TRAVEL_ID = t.ID
INNER JOIN flight f ON f.ID = tf.FLIGHT_ID
GROUP BY t.NUM
有办法少做它。我愿意做一个UNION 2所选择的,就像这样:
SELECT `travel`.`ID` as 'TRAVEL_ID',`travel`.`NUM` as 'TRAVEL_NUM', `flight`.`NUM` as 'FLIGHT_NUM1',`flight`.`DEPARTURE_DATE`as 'FLIGHT_DEPARTURE_DATE',`flight`.`NUM`as 'FLIGHT_NUM2',`flight`.`ARRIVAL_DATE`as 'FLIGHT_ARRIVAL_DATE'
FROM `travel`
INNER JOIN `travel_has_flight` on `travel`.`ID`=`travel_has_flight`.`travel_ID`
INNER JOIN `flight` on `travel_has_flight`.`flight_ID`=`flight`.`ID`
WHERE `travel`.ID in (SELECT travel_ID FROM `travel_has_flight` GROUP BY travel_ID HAVING count(`flight_ID`)=1)
UNION
SELECT T.TRAVEL_ID,T.TRAVEL_NUM,MIN(T.FLIGHT_NUM1),MIN(T.FLIGHT_DEPARTURE_DATE),MAX(T.FLIGHT_NUM2),MAX(T.FLIGHT_ARRIVAL_DATE)
FROM
(SELECT `travel`.`ID` as 'TRAVEL_ID',`travel`.`NUM` as 'TRAVEL_NUM', `flight`.`NUM` as 'FLIGHT_NUM1',`flight`.`DEPARTURE_DATE`as 'FLIGHT_DEPARTURE_DATE',`flight`.`NUM`as 'FLIGHT_NUM2',`flight`.`ARRIVAL_DATE`as 'FLIGHT_ARRIVAL_DATE'
FROM `travel`
INNER JOIN `travel_has_flight` on `travel`.`ID`=`travel_has_flight`.`travel_ID`
INNER JOIN `flight` on `travel_has_flight`.`flight_ID`=`flight`.`ID`
WHERE `travel`.ID in (SELECT travel_ID FROM `travel_has_flight` GROUP BY travel_ID HAVING count(`flight_ID`)>1)) as T
GROUP BY T.TRAVEL_ID,T.TRAVEL_NUM
你的表,
table travel (ID int, NUM varchar())
table leg (travel_ID int, flight_ID)
table flight (ID int, NUM varchar(), DEPARTURE date, ARRIVAL date)
加入旅游与腿,飞行加盟腿,
select <more below>
from travel t
<how> join leg l where t.ID = l.travel_ID
<how> join flight f where l.flight_ID == f.ID
现在,决定这些是内部连接还是外部连接?
即使在travel和travel_has_flight(腿)之间加入时没有匹配,您还想要结果吗?如果不是,则使用内连接,如果是,则使用外连接。 (对您的样本数据很重要)
即使在travel_has_flight(腿)和航班之间的联接没有匹配时,您还想要结果吗?如果不是,则使用内连接,如果是,则使用外连接。 (不要紧,你的样本数据)
你说使用内部连接,
select <more below>
from travel t
inner join leg l where t.ID = l.travel_ID
inner join flight f where l.flight_ID == f.ID
在您的样本数据,结果集是,
t. | t. | l. | l. | f. | f. | f. | f.
ID | NUM | travel_ID | flight_ID | ID | NUM | DEPARTURE | ARRIVAL
01 | N01 | 01 | 01 | 01 | A01 | 2010-10-10 12:50 | 2010-10-10 17:50
01 | N01 | 01 | 02 | 02 | A02 | 2010-10-17 14:50 | 2010-10-17 14:50
02 | N02 | 02 | 03 | 03 | A03 | 2010-10-22 12:50 | 2010-10-22 17:50
但是,你说使用内部连接,所以您正在寻找1:1 您需要按日期排序的这些数据。假设理智数据(始发< =到达)
with (
select t.ID as tid, t.NUM as tnum, f.DEPARTURE as dep, f.ARRIVAL as arr
from travel t
inner join leg l where t.ID = l.travel_ID
inner join flight f where l.flight_ID == f.ID
group by t.NUM
order by t.NUM, DEPARTURE
) as parts,
<query>
现在,你将如何在两行合并在上面的“零件”表的连接部位?
谢谢你的解释。在我的情况下,我使用mariadb 10.1.19,它无法在嵌套表/子查询上工作。更新到10.1.21解决了查询问题。 – RUTKLUK
你是否为一家航空公司工作? – Shaggy
试图升级我的第三方计费系统以适应我的需求? – RUTKLUK