车辆出入库姆省三类场景SQL查询及错误修正需求

错误原因 #

1. 表名拼写错误：通行记录表实际名为Trafic，错误SQL中误写为Traffic，会直接触发表不存在的报错。
2. 关联逻辑错误：用隐式笛卡尔积关联两个CTE后直接输出全表字段，会把同一车辆的进入、离开记录拆分为两行输出，还会出现字段重复的问题，不符合查询需求。

正确SQL写法 #

场景1：查询所有既进入又离开库姆省的车辆的全部通行记录

WITH enter_qom AS
(
    SELECT DISTINCT CarPelakNumber 
    FROM Trafic
    WHERE FromCity IN (SELECT City FROM City) 
      AND ToCity = 'Qom' 
),
leave_qom AS
(
    SELECT DISTINCT CarPelakNumber 
    FROM Trafic
    WHERE FromCity = 'Qom' 
      AND ToCity IN (SELECT City FROM City)
)
SELECT * FROM Trafic
WHERE CarPelakNumber IN (
    SELECT enter_qom.CarPelakNumber 
    FROM enter_qom 
    INNER JOIN leave_qom ON enter_qom.CarPelakNumber = leave_qom.CarPelakNumber
)

运行结果和你贴出的错误输出的记录一致，仅过滤掉了仅进入、仅离开的车辆记录。

场景2：同车辆的进出记录合并为一行展示（这类需求的常规预期输出）

WITH enter_qom AS
(
    SELECT 
        CarPelakNumber, 
        id AS enter_record_id,
        FromCity AS depart_city_before_enter
    FROM Trafic
    WHERE FromCity IN (SELECT City FROM City) 
      AND ToCity = 'Qom' 
),
leave_qom AS
(
    SELECT 
        CarPelakNumber, 
        id AS leave_record_id,
        ToCity AS arrive_city_after_leave
    FROM Trafic
    WHERE FromCity = 'Qom' 
      AND ToCity IN (SELECT City FROM City)
)
SELECT 
    e.CarPelakNumber,
    e.enter_record_id,
    e.depart_city_before_enter,
    l.leave_record_id,
    l.arrive_city_after_leave
FROM enter_qom e
INNER JOIN leave_qom l ON e.CarPelakNumber = l.CarPelakNumber

运行结果：

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