如何组合sql Count查询 [英] How to combine sql Count queries

问题描述

select t.TruckNo ,count(jt.JobID)as OnTimekpi
 from tbl_pms_JobOrders_Trucks_Info jt
inner join tbl_pms_JobOrders j on j.JobID = jt.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
where  j.WarehouseID=225 and j.Planneddate between '20131201' and '20140131' and t.TruckID != 312 and t.IsDeleted != 'true'
       and jt.ETA >= d.DeliveryTime
       group by  t.TruckNo,jt.StausCode

       select t.TruckNo,COUNT(jt.JobID)as OFFTimekpi from tbl_pms_JobOrders_Trucks_Info jt
inner join tbl_pms_JobOrders j on j.JobID = jt.JobID
Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
Inner Join tbl_pms_ShipmentInfo si on si.ShipmentID = jt.ShipmentID
Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
where  j.WarehouseID=225 and j.Planneddate between '20131201' and '20140131' and t.TruckID != 312 and t.IsDeleted != 'true'
       and jt.ETA < d.DeliveryTime group by t.TruckNo,jt.StausCode

注意：从First Select声明我得到31条记录，从Second得到17条记录



谢谢提前

Note: From First Select statement i got 31 records and from Second i got 17 records

Thanks advance

推荐答案

只需取出ETA条件。



目前，两个查询之间存在一个区别：

Just take out the ETA condition.

At the moment, you have one difference between the two queries:

and jt.ETA >= d.DeliveryTime

and jt.ETA < d.DeliveryTime

由于每个都排除了另一个，你可以完全删除条件并获得所有结果。

Since each excludes the other, you can remove teh condition completely and get all the results.

这篇关于如何组合sql Count查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！