MySQL - join tables

Question

我需要从以下两个表中对结果表进行选择：

flight_connection

航班号	出发	到达
310	NUE	TXL
926	FRA	NUE
312	TXL	NUE

您可以在没有后缀的情况下执行此操作，只需两个连接

CREATE TABLE flight_connection (
`flight number` INTEGER,
`departure` VARCHAR(3),
`arrival` VARCHAR(3)
);
INSERT INTO flight_connection
(`flight number`, `departure`, `arrival`)
VALUES
('310', 'NUE', 'TXL'),
('926', 'FRA', 'NUE'),
('312', 'TXL', 'NUE');

CREATE TABLE airport (
`code` VARCHAR(3),
`description` VARCHAR(12)
);
INSERT INTO airport
(`code`, `description`)
VALUES
('NUE', 'Nuremberg'),
('FRA', 'Frankfurt'),
('TXL', 'Berlin-Tegel');

SELECT `code`,`description`,IFNULL(departures,0) as departures,IFNULL(arrivals,0) as arrivals FROM airport a
LEFT JOIN (SELECT COUNT(*) arrivals,`arrival` FROM flight_connection GROUP BY `arrival`) f1 ON a.`code` = f1.`arrival` 
LEFT JOIN (SELECT COUNT(*) departures,`departure` FROM flight_connection GROUP BY `departure`) f2 ON a.`code` = f2.`departure`

代码 | 描述 | 出发 | 到达 :--- |:----------- |---------: |-------: 努伊 |纽伦堡 |         1 |       2 法国 |法兰克福 |         1 |       0 TXL |柏林-泰格尔 |         1 |       1

db<>在这里小提琴

mysql 使用 2 个 SQL，如 PostgreSQL(见下文)，将 FULL OUTER JOIN 替换为第一个的左连接，将第二个替换为右连接。

并使用这个答案

如何在MySQL中进行完整的外部连接？

Postgresql

联合是"合并"行，连接列

SELECT code,desciption,departures, arrivals FROM
( SELECT DISTINCT code, description, Count(departure) AS departures FROM 
(
SELECT code, description, departure FROM airport
INNER JOIN flight_connection ON departure = code
) as tmptbl
GROUP BY code, description ) r1
FULL OUTER JOIN 
(  SELECT DISTINCT code, description, Count(arrival) AS arrivals FROM 
(
SELECT code, description, arrivalFROM airport
INNER JOIN flight_connection ON arrival = code
) as tmptbl
GROUP BY code, description ) r2
USING (code,description);

如果需要，可以使用合并函数将 Null 更改为 0。