如何调整T-SQL查询以支持多条Assembly Line的出入记录统计

原查询仅针对MAIN生产线统计产品的进出时间，现在需要扩展为支持多条生产线，展示每条生产线对应的产品进出记录。以下是调整后的完整代码及关键修改说明：

调整后的T-SQL代码 #

SELECT 
    COALESCE(Enters.ToAssemblyLineName, Exits.FromAssemblyLineName) AS AssemblyLine,
    COALESCE(Enters.ChassisNumber, Exits.ChassisNumber) AS ChassisNumber,
    Enters.MinTime AS EnterTime,
    Exits.MaxTime AS ExitTime
FROM
(
    -- ENTERS: 从其他生产线进入当前生产线的最早时间
    SELECT
        ToAssemblyLineName,
        ChassisNumber,
        MIN(TimeStamp) AS MinTime
    FROM DIM.ChassisMoveLog
    WHERE TimeStamp >= CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)
        AND TimeStamp < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
        AND FromAssemblyLineName <> ToAssemblyLineName  -- 确保是跨生产线进入
        AND PhysicalLocation = 'Chillicothe'
    GROUP BY ToAssemblyLineName, ChassisNumber
) Enters
FULL OUTER JOIN
(
    -- EXITS: 从当前生产线离开到其他生产线的最晚时间
    SELECT
        FromAssemblyLineName,
        ChassisNumber,
        MAX(TimeStamp) AS MaxTime
    FROM DIM.ChassisMoveLog
    WHERE TimeStamp >= CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATETIME)
        AND TimeStamp < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
        AND FromAssemblyLineName <> ToAssemblyLineName  -- 确保是跨生产线离开
        AND PhysicalLocation = 'Chillicothe'
    GROUP BY FromAssemblyLineName, ChassisNumber
) Exits
ON Enters.ChassisNumber = Exits.ChassisNumber
    AND Enters.ToAssemblyLineName = Exits.FromAssemblyLineName  -- 按生产线匹配进出记录
ORDER BY AssemblyLine ASC, EnterTime ASC, ExitTime ASC;

关键修改点说明 #

1. Enters子查询调整

   • 移除硬编码的ToAssemblyLineName = 'MAIN'条件，改用FromAssemblyLineName <> ToAssemblyLineName筛选所有跨生产线进入的记录
   • 将ToAssemblyLineName加入SELECT和GROUP BY，作为生产线的标识字段

2. Exits子查询调整

   • 移除硬编码的FromAssemblyLineName = 'MAIN'条件，同样用FromAssemblyLineName <> ToAssemblyLineName筛选跨生产线离开的记录
   • 将FromAssemblyLineName加入SELECT和GROUP BY，用来关联对应的生产线

3. JOIN逻辑优化

   • 除了匹配产品编号ChassisNumber，新增生产线名称的匹配条件（Enters.ToAssemblyLineName = Exits.FromAssemblyLineName），确保同一条生产线的进出记录正确关联

4. AssemblyLine列取值修正

   • 不再使用固定字符串，改用COALESCE(Enters.ToAssemblyLineName, Exits.FromAssemblyLineName)获取生产线名称，避免因只有进入/离开记录导致生产线名称为空

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