如何关联两个SQL查询实现订单对应已完成零件数统计？

我先写了一个基础SQL查询，用来统计每个订单在Shipping工位的已完成零件数：

SELECT r1.OrderNo, COUNT(r1.PartNo) AS [Pcs Complete] 
FROM OrderRouting r1 
WHERE r1.Status = 'Current' AND r1.WorkCntr = 'Shipping' 
GROUP BY r1.OrderNo

这个查询能输出按订单号分组的零件统计结果。

接下来我想把这个统计列整合到一个更复杂的外部查询的SELECT列表中，于是编写了如下语句：

SELECT CAST(r.OrderNo AS INT) AS OrderNo, 
       ( SELECT r1.OrderNo, COUNT(r1.PartNo) 
         FROM OrderRouting r1 
         WHERE r1.Status = 'Current' AND r1.WorkCntr = 'Shipping' 
         GROUP BY r1.OrderNo ) AS [Pcs Complete] 
FROM OrderRouting r 
JOIN OrderDet od ON r.JobNo = od.JobNo 
JOIN Orders o ON od.OrderNo = o.Orderno 
WHERE r.Status = 'Current' AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping') 
GROUP BY CAST(r.OrderNo AS INT)

显然这个语句无法运行——因为子查询只能返回一列。如果我把子查询里的r1.OrderNo去掉，聚合结果会被统一应用到所有订单，这完全不符合我的预期。

之后我又尝试了两种写法，都没能成功关联：

写法一：

SELECT CAST(r.OrderNo AS INT) AS OrderNo, 
       ( SELECT COUNT(r1.PartNo) 
         FROM OrderRouting r1 
         WHERE r1.Status = 'Current' AND r.WorkCntr = 'Shipping' ) AS [Pcs Complete] 
FROM OrderRouting r 
JOIN OrderDet od ON r.JobNo = od.JobNo 
JOIN Orders o ON od.OrderNo = o.Orderno 
WHERE r.Status = 'Current' AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping') 
GROUP BY CAST(r.OrderNo AS INT)

这个写法的输出结果完全不对，所有订单的统计数值都相同，没有和对应的OrderNo关联起来。

写法二：

SELECT CAST(r.OrderNo AS INT) AS OrderNo, 
       ( SELECT COUNT(r1.PartNo) 
         FROM OrderRouting r1 
         JOIN OrderRouting r ON r1.OrderNo = r.OrderNo 
         WHERE r1.Status = 'Current' AND r.WorkCntr = 'Shipping' ) AS [Pcs Complete] 
FROM OrderRouting r 
JOIN OrderDet od ON r.JobNo = od.JobNo 
JOIN Orders o ON od.OrderNo = o.Orderno 
WHERE r.Status = 'Current' AND r.WorkCntr IN ('Cutting', 'Framing', 'Assembly', 'Shipping') 
GROUP BY CAST(r.OrderNo AS INT)

同样，这个写法也没能把聚合结果和外部查询的OrderNo正确绑定，达不到预期效果。

请问我该如何正确关联这两个查询，让每个订单都能对应到它在Shipping工位的零件统计数呢？

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