如何编写无空值的两表关联SQL查询（禁用WITH、crosstab等）

我有两张表：Airport表和AirportTranslation表，当前关联查询会返回两行含null值的结果，需要编写SQL查询得到无null值的单行结果，且不能使用WITH、crosstab等语法。

表结构 #

Airport表 #

+ -------------------------------------+-------------+-------------+
| ID                                   | IATA        | ICAO        | 
+--------------------------------------+-------------+-------------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH         | SPPY        | 
+--------------------------------------+-------------+-------------+

AirportTranslation表 #

+ -------------------------------------+--------------------------------------+------+-------------+
| ID                                   | PARENTID                             | LANG | VALUE       |
+--------------------------------------+--------------------------------------+------+-------------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c66 | 01d491eb-7d00-410c-9c19-eb23d83f9c65 | ESP  | CHACHAPOYAS |
+--------------------------------------+--------------------------------------+------+-------------+
| eb06eb88-0530-4051-b4eb-3a04a01bcafe | 01d491eb-7d00-410c-9c19-eb23d83f9c65 | ENG  | CHACHAPOYAS |
+--------------------------------------+--------------------------------------+------+-------------+

现有查询及结果 #

现有查询语句 #

SELECT
public."Airports"."ID",
public."Airports"."IATA", 
public."Airports"."ICAO",
CASE WHEN public."AirportTranslation"."Lang" = 'ESP' THEN public."AirportTranslation"."Value" ELSE null END as "ESP",
CASE WHEN public."AirportTranslation"."Lang" = 'EN' THEN public."AirportTranslation"."Value" ELSE null END as "EN"
FROM public."Airports" JOIN 
public."AirportTranslation" ON public."Airports"."ID" = public."AirportTranslation"."PARENTID"

现有查询结果 #

+ -------------------------------------+-----+------+-------------+-----------+
| ID                                   | IATA| ICAO | ESP         | EN        |
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | null        |CHACHAPOYAS|
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | CHACHAPOYAS |null       |
+--------------------------------------+-----+------|-------------+-----------+

期望结果 #

+ -------------------------------------+-----+------+-------------+-----------+
| ID                                   | IATA| ICAO | ESP         | EN        |
+--------------------------------------+-----+--------------------+-----------+
| 01d491eb-7d00-410c-9c19-eb23d83f9c65 | CHH | SPPY | CHACHAPOYAS |CHACHAPOYAS|
+--------------------------------------+-----+--------------------+-----------+

解决方案 #

通过分组查询配合聚合函数MAX()忽略null值，合并同一机场的多行结果：

SELECT
    public."Airports"."ID",
    public."Airports"."IATA", 
    public."Airports"."ICAO",
    MAX(CASE WHEN public."AirportTranslation"."Lang" = 'ESP' THEN public."AirportTranslation"."Value" END) as "ESP",
    MAX(CASE WHEN public."AirportTranslation"."Lang" = 'ENG' THEN public."AirportTranslation"."Value" END) as "EN"
FROM public."Airports" 
JOIN public."AirportTranslation" ON public."Airports"."ID" = public."AirportTranslation"."PARENTID"
GROUP BY public."Airports"."ID", public."Airports"."IATA", public."Airports"."ICAO"

说明 #

1. MAX()函数会自动忽略null值，提取分组内对应语言的有效翻译值；
2. 修正了原查询中英文匹配条件（表中存储为ENG而非EN），确保能正确获取结果；
3. GROUP BY将同一机场的多行数据合并为一行，分组字段需包含Airports表中所有非聚合字段。

内容的提问来源于stack exchange，提问作者user6408649