本文介绍了如何减少此Sql查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Select * from
(Select t.TruckNo, COUNT(j.Ordernumber) as Invoice,COUNT(distinct si.ShipmentNo) as ManiFest from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
Group By TruckNo) a,
(Select Count(jt.StausCode) as Delivered from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
and jt.StausCode=2) b,
(Select Count(jt.StausCode) as UnDelivered from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
and jt.StausCode=3) c,
(Select Count(jt.StausCode) as PartialDelivered from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
and jt.StausCode=4) d,
(Select Count(jt.StausCode) as Pending from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
and jt.StausCode = 1 and jt.StausCode = 77)e推荐答案
Select * from
(Select t.TruckNo, COUNT(j.Ordernumber) as Invoice,COUNT(distinct si.ShipmentNo) as ManiFest,
'Delivered'=case when jt.StausCode=2 then Count(jt.StausCode) else 0 end,
'UnDelivered'=case when jt.StausCode=3 then Count(jt.StausCode) else 0 end,
'PartialDelivered'=case when jt.StausCode=4 then Count(jt.StausCode) else 0 end,
'Pending'=case when jt.StausCode=5 then Count(jt.StausCode) else 0 end
from tbl_pms_JobOrders j
Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
--Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Where j.WarehouseID=225
--and t.TruckNo = 'W1217B'
and j.Planneddate between '20131201' and '20140131'
and t.TruckID != 312
and t.IsDeleted != 'true'
Group By TruckNo) a,..
这篇关于如何减少此Sql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！