如何基于另一表的唯一组合筛选表A行,是否可跳过中间表直接获取结果
问题解答
方案1:基于已有的表B查询目标结果
直接通过多字段内连接匹配表A和表B的组合即可,SQL语句如下:
SELECT a.* FROM 表A a INNER JOIN 表B b ON a.Origin = b.Origin AND a.Destination = b.Destination AND a.Carrier = b.Carrier AND a.Class = b.Class;
执行后即可得到你需要的输出结果。
方案2:跳过创建表B,直接从表A查询结果
你可以通过CTE(公共表表达式)分步统计符合要求的组合,无需额外创建物理表B,SQL语句如下:
WITH class_passenger_stats AS ( -- 第一步:统计每个(起终点、航司、舱位)组合的乘客数,过滤人数不足100的组合 SELECT Origin, Destination, Carrier, Class, COUNT(*) AS passenger_count FROM 表A GROUP BY Origin, Destination, Carrier, Class HAVING COUNT(*) > 100 ), valid_route_carrier AS ( -- 第二步:筛选出同时包含经济舱、高端舱两种舱位的(起终点、航司)组合 SELECT Origin, Destination, Carrier FROM class_passenger_stats WHERE Class IN ('Econ', 'Premium') GROUP BY Origin, Destination, Carrier HAVING COUNT(DISTINCT Class) = 2 ) -- 第三步:关联原表获取所有符合要求的行 SELECT a.* FROM 表A a INNER JOIN valid_route_carrier v ON a.Origin = v.Origin AND a.Destination = v.Destination AND a.Carrier = v.Carrier WHERE a.Class IN ('Econ', 'Premium');
如果你的数据库支持窗口函数,还可以使用更简洁的写法:
SELECT * FROM ( SELECT a.*, SUM(CASE WHEN Class = 'Econ' THEN 1 ELSE 0 END) OVER(PARTITION BY Origin, Destination, Carrier) AS econ_passenger_cnt, SUM(CASE WHEN Class = 'Premium' THEN 1 ELSE 0 END) OVER(PARTITION BY Origin, Destination, Carrier) AS premium_passenger_cnt FROM 表A a ) t WHERE econ_passenger_cnt > 100 AND premium_passenger_cnt > 100 AND Class IN ('Econ', 'Premium');
逻辑说明
两种写法的逻辑完全对齐表B的生成规则:先校验每个(起终点+航司)组合的两种舱位乘客数均超过100,再返回原表中对应组合的所有行,和先建表B再关联的输出结果完全一致。
内容的提问来源于stack exchange,提问作者Green Finance
相关产品推荐
相关产品推荐

