select *,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='10') as WaitingShippedLoadScan,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='20') as ShippedLoadScan
from WMS_Shipment a where a.Id in (select ShipmentId from WMS_Package where Status='10' or Status='20' group by ShipmentId)

select *,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='10') as WaitingShippedLoadScan,
(select COUNT(1) from WMS_Package where ShipmentId=a.Id and Status='20') as ShippedLoadScan
from WMS_Shipment a where 
EXISTS (SELECT 1 from WMS_Package where a.Id=ShipmentId Status='10' or Status='20' group by ShipmentId)

------解决方案--------------------

SQL code


select *,b.WaitingShippedLoadScan,b.ShippedLoadScan
from WMS_Shipment a , 
(    select ShipmentId,
    sum(case when Status=10 then 1 else 0 end)as WaitingShippedLoadScan,
    sum(case when Status=20 then 1 else 0 end)as ShippedLoadScan,
   from WMS_Package where Status='10' or Status='20' 
   group by ShipmentId
)b where a.Id =b.ShipmentId

--首先 这个看似可能快点，但是有可能执行计划和你写的一样
--其次
--1、如果Status是数值类型的那么不要给10加引号，如果是字符串的要加上引号，保持字段类型和变量类型一致
--2、id 和ShipmentId 要有索引会更快