Union all 和 Order by

如果每个Union 的结果集的排序规则是不一样的

那么要在结果集 外面套一层 而且内部要用 top 99.9999999 percent

例如:

 

 select * from (  select top 99.9999999 percent t0.JPlanNo as JPlanNo,t0.jbillno as JPlanSchedulingBillNo,
t0.JPlanID as JPlanID,
t0.JOrderByNo as JPlanOrderByNo,
tt.id as JProcessBillID,
tt.BillNO as JProcessBillNo,
tt.JProcessID as JProcessID,
t1.name as JProcessName,
tt.JOperSN as JOperSN,
tt.JPlanQty as JPlanQty,
tt.JQty as JQty,
tt.JEquipmentID as JEquipmentID,
t2.name as JEquipmentName,
tt.JProductionCenterID as JProductionCenterID,
t3.name as JProductionCenterName,
tt.JRemark as JRemark,
tt.JPlanStartDate as JPlanStartDate,
tt.JPlanEndDate as JPlanEndDate,
tt.JCheckedQty as JCheckedQty,
tt.JUnCheckedQty as JUnCheckedQty,
tt.JDisPatchQty as JDisPatchQty,
tt.JDeliveryDate as JDeliveryDate,
tt.JSugDeliveryDate as JSugDeliveryDate,
tt.JLPBMaterialID as JLPBMaterialID,
t4.Name as JLPBMaterialName ,
t4.JStandard as JStandard,
t4.JModel as JModel,
t5.id as JProcessBillSchedulingID
from JL_M_ProcessBill tt
inner join JPlanScheduling t0 on t0.JPlanID = tt.JLMProductionPlanID
inner join JL_PM_PRM_Process t1 on tt.JProcessID = t1.id
inner join JL_P_B_Equipment t2 on t2.id = tt.JEquipmentID
left join JL_ProductionCenter t3 on t3.id = tt.JProductionCenterID
left join JL_P_B_Material t4 on t4.id = tt.JLPBMaterialID
left join JProcessBillScheduling t5 on t5.JProcessBillID = tt.id
where t1.id = 32
order by tt.JPlanQty , tt.JOperSN , t0.JOrderByNo
       ) t3               
    union all  
select * from (  select top 99.9999999 percent t0.JPlanNo as JPlanNo,t0.jbillno as JPlanSchedulingBillNo,
t0.JPlanID as JPlanID,
t0.JOrderByNo as JPlanOrderByNo,
tt.id as JProcessBillID,
tt.BillNO as JProcessBillNo,
tt.JProcessID as JProcessID,
t1.name as JProcessName,
tt.JOperSN as JOperSN,
tt.JPlanQty as JPlanQty,
tt.JQty as JQty,
tt.JEquipmentID as JEquipmentID,
t2.name as JEquipmentName,
tt.JProductionCenterID as JProductionCenterID,
t3.name as JProductionCenterName,
tt.JRemark as JRemark,
tt.JPlanStartDate as JPlanStartDate,
tt.JPlanEndDate as JPlanEndDate,
tt.JCheckedQty as JCheckedQty,
tt.JUnCheckedQty as JUnCheckedQty,
tt.JDisPatchQty as JDisPatchQty,
tt.JDeliveryDate as JDeliveryDate,
tt.JSugDeliveryDate as JSugDeliveryDate,
tt.JLPBMaterialID as JLPBMaterialID,
t4.Name as JLPBMaterialName ,
t4.JStandard as JStandard,
t4.JModel as JModel,
t5.id as JProcessBillSchedulingID
from JL_M_ProcessBill tt
inner join JPlanScheduling t0 on t0.JPlanID = tt.JLMProductionPlanID
inner join JL_PM_PRM_Process t1 on tt.JProcessID = t1.id
inner join JL_P_B_Equipment t2 on t2.id = tt.JEquipmentID
left join JL_ProductionCenter t3 on t3.id = tt.JProductionCenterID
left join JL_P_B_Material t4 on t4.id = tt.JLPBMaterialID
left join JProcessBillScheduling t5 on t5.JProcessBillID = tt.id
where t1.id = 39
order by tt.JPlanEndDate , t0.JOrderByNo
       ) t4  

转载于:https://www.cnblogs.com/YyuTtian/p/4560977.html