如何从两个公司价格表筛选指定航线最低价格并合并？

假设两张表分别名为table_a（存储公司A的机票数据）和table_b（存储公司B的机票数据），可以通过以下方式实现需求：

方案一：合并数据后统一筛选 #

先将两张表中符合指定航线的数据合并，再按公司分组提取最低价格：

SELECT 
    Company,
    'BANGKOK' AS `Start city`,
    'OSLO' AS `Stop city`,
    MIN(Price) AS Price
FROM (
    SELECT Company, `Start city`, `Stop city`, Price
    FROM table_a
    WHERE `Start city` = 'BANGKOK' AND `Stop city` = 'OSLO'
    UNION ALL
    SELECT Company, `Start city`, `Stop city`, Price
    FROM table_b
    WHERE `Start city` = 'BANGKOK' AND `Stop city` = 'OSLO'
) AS combined_data
GROUP BY Company;

方案二：分别处理后合并结果 #

对每张表单独筛选指定航线并提取最低价格，再合并两个结果集：

-- 提取公司A的指定航线最低价格
SELECT Company, `Start city`, `Stop city`, MIN(Price) AS Price
FROM table_a
WHERE `Start city` = 'BANGKOK' AND `Stop city` = 'OSLO'
GROUP BY Company, `Start city`, `Stop city`

UNION ALL

-- 提取公司B的指定航线最低价格
SELECT Company, `Start city`, `Stop city`, MIN(Price) AS Price
FROM table_b
WHERE `Start city` = 'BANGKOK' AND `Stop city` = 'OSLO'
GROUP BY Company, `Start city`, `Stop city`;

两种方案都能得到你期望的结果，可根据实际表结构和业务场景选择使用。

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