如何在没有Union的情况下加入select语句 [英] How to join select statements without Union

问题描述

Select j.Ordernumber,s.ShipmentNo,t.TruckNo,j.Route,j.CustomerName,j.Ordertype,
            d.DeliveryDate,d.DeliveryTime,Convert(varchar(35),jt.StausCode)as StausCode,j.JobID,'Active' as PStatus
            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_ShipmentInfo s on s.ShipmentID=jt.ShipmentID
            Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
            Where j.WarehouseID=@WarehouseID and jt.StausCode != 1 and jt.StausCode !=77
            and j.JobID not in (Select JobID from tbl_pms_POD_Submit)
            and jt.TruckID = @TruckID
            and jt.TruckID != 312
            and d.recno in (select max (recno) from tbl_pms_DeliveryInfo where JobID in ( select JobID from tbl_pms_JobOrders_Trucks_Info where TruckID=@TruckID and JobID in (select JobID from tbl_pms_JobOrders where WarehouseID=@WarehouseID)) group by JobID)

       Union


        Select j.Ordernumber,s.ShipmentNo,t.TruckNo,j.Route,j.CustomerName,j.Ordertype,
            d.DeliveryDate,d.DeliveryTime,Convert(varchar(35),jt.StausCode)as StausCode,j.JobID,'Committed' as PStatus
            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_ShipmentInfo s on s.ShipmentID=jt.ShipmentID
            Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
            Where j.WarehouseID=@WarehouseID and jt.StausCode != 1 and jt.StausCode !=77
            and j.JobID  in (Select JobID from tbl_pms_POD_Submit)
            and jt.TruckID = @TruckID
            and jt.TruckID != 312
            and d.recno in (select max (recno) from tbl_pms_DeliveryInfo where JobID in ( select JobID from tbl_pms_JobOrders_Trucks_Info where TruckID=@TruckID and JobID in (select JobID from tbl_pms_JobOrders where WarehouseID=@WarehouseID)) group by JobID)

这是我对joborder表中记录状态的查询提交到pod表并提交到pod表。



i以这种方式查询上面..



i面临一个问题，它有点慢....有什么替代上面的查询



plz帮帮我..谢谢Advance：）

This my query for status of records in joborder table which are not submitted to pod table and which are submitted to pod table .

i made the query in this way Above..

i facing a problem that it is a bit slow .... is there any alternative for above query

plz help me ..Thanks Advance :)

推荐答案

唯一的区别似乎是它可以在表tbl_pms_POD_Submit中找到。 （从tbl_pms_POD_Submit中选择JobID）。您可以简单地将它放在单个查询中，而不是联合，并在tbl_pms_POD_Submit上执行外连接，并在结果中选择JobID。当JobID被填充时，您知道它是活动的，否则它被提交。一个很大的优点是你也可以使用活动的JobId，以防你需要它们。



如其中一条评论中所述，使用配置文件检查缺失的索引。



祝你好运！

The only difference seems to be that it can be found in table tbl_pms_POD_Submit. (Select JobID from tbl_pms_POD_Submit). Instead of a union you could simply put it in a single query and do an outer join on tbl_pms_POD_Submit and select the JobID in the result. When the JobID is filled you know it is active, otherwise it is committed. A big advantage is that you also have the JobId's of the active ones just in case you might need them.

As already mentioned in one of the comments, use the profiles to check for missing indexes.

Good luck!

这篇关于如何在没有Union的情况下加入select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！