SQL按PostingType条件对单列存储的估算、实际机器工时做转置分栏统计

• 相关子查询缺少作业维度关联：所有嵌套的标量子查询仅通过PostingType做筛选，没有关联外层聚合出的当前JobNo，导致每个作业的统计结果实际是全表符合过账类型的全局汇总值，和当前作业完全无关。
• 筛选口径不统一：外层通过StartDateAndTime划定了统计的时间范围，但所有子查询没有继承这个时间过滤条件，会把不在目标周期内的记录纳入统计，和外层的统计范围错位。
• 机器名提取逻辑存在硬伤：估算和实际机器名的提取分别依赖冒号、空格作为分隔符，没有做容错处理——如果某条工序描述里不存在对应分隔符，CHARINDEX会返回0，直接触发字符串截取的报错；同时因为没有关联当前作业，TOP 1取到的是全表第一条匹配的工序描述，不是当前作业的对应机器信息。
• 时间边界设置错误：2021-04-29T59:59:00.000是不存在的非法时间值，常规一天的最大时刻为23:59:59，该条件会导致部分当天的实际加工记录无法被正确筛选。

推荐使用条件聚合替代逐字段嵌套子查询的写法，仅需一次表扫描就能完成所有维度的统计，相比原代码的多轮子查询查询性能提升非常明显，逻辑口径也更易统一。
优化后的参考SQL如下：

SELECT 
    JobNo,
    -- 估算维度列
    MAX(CASE 
        WHEN PostingType = '1' 
        THEN LEFT(OperationDesc, CHARINDEX(':', OperationDesc + ':') - 1) 
    END) AS MachineEstimated,
    SUM(CASE WHEN PostingType = '1' THEN OperationLength ELSE 0 END) AS EstimatedTime,
    SUM(CASE 
        WHEN PostingType = '1' AND OperationDesc NOT LIKE '%MR%' 
        THEN Quantity ELSE 0 
    END) AS QuantityOrdered,
    -- 实际维度列
    MAX(CASE 
        WHEN PostingType = '2' 
        THEN LEFT(OperationDesc, CHARINDEX(' ', OperationDesc + ' ') - 1) 
    END) AS MachineUsed,
    SUM(CASE WHEN PostingType = '2' THEN OperationLength ELSE 0 END) AS TotalTime,
    SUM(CASE WHEN PostingType = '2' THEN Quantity ELSE 0 END) AS TotalQuantity
FROM TSE
WHERE 
    StartDateAndTime >= '2021-04-28T06:00:00.000' 
    -- 原条件写的'2021-04-29T59:59:00.000'为非法时间值，调整为半开区间<4月30日零点，即可覆盖4月29日全天所有时刻，兼容任意时间精度
    AND StartDateAndTime < '2021-04-30T00:00:00.000'
GROUP BY JobNo

优化说明 #

• 所有统计分支自动继承外层的时间筛选条件，不会出现全局汇总的口径偏差。
• 机器名提取环节给原始工序描述拼接了对应分隔符，避免因找不到分隔符导致的LEFT截取报错。
• 全流程仅对TSE表做一次扫描，消除了原代码中每个字段单独查询一次源表的重复IO开销，作业数据量越大性能优势越突出。

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