高手来看看··如何优化这条sql语句！怎样效率最好

高手来看看··怎么优化这条sql语句！怎样效率最好！

SQL code

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 ShippedLoadScanfrom WMS_Shipment a where a.Id in (select ShipmentId from WMS_Package where Status='10' or Status='20' group by ShipmentId)

SQL code

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 ShippedLoadScanfrom 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.ShippedLoadScanfrom 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 要有索引会更快