27,579
社区成员
发帖
与我相关
我的任务
分享
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 *,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 要有索引会更快
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)