如何减少此Sql查询？ [英] How Can I Reduce This Sql Query?

问题描述

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查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！