select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
,case when a.Contract_Status=10 then '初始創建'when a.Contract_Status=20 then '合同作廢'
when a.Contract_Status=30 then '審核完畢' end StatusName
,case when a.Old_Contract_KeyId is null
and a.Contract_Status = 30
and not exists(select * from Contract_Income_Contract where Old_Contract_KeyId = a.keyid and Is_Delete=0) then 1 else 0 end IsDifferAdjust
from Contract_InCome_Contract a
left join Dic_Contract_Type c on a.Dic_Contract_Type=c.keyid
left join Dic_Organization d on a.PartyA=d.keyid
left join Dic_Customer_Trans e on a.PartyB=e.keyid
left join Device_Vehicle f on a.fk_vehicle=f.keyid
left join Device_Vehicle g on a.fk_trailer=g.keyid
where a.keyid is not null
紅色部分明顯影響了查詢速度，有更好的優化方法嗎
uj5u.com熱心網友回復：
子查詢換成表連接，再試試
select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
,case when a.Contract_Status=10 then '初始創建'when a.Contract_Status=20 then '合同作廢'
when a.Contract_Status=30 then '審核完畢' end StatusName
case when a.Old_Contract_KeyId is null and a.Contract_Status = 30 and h.Old_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
from Contract_InCome_Contract a
left join Dic_Contract_Type c on a.Dic_Contract_Type=c.keyid
left join Dic_Organization d on a.PartyA=d.keyid
left join Dic_Customer_Trans e on a.PartyB=e.keyid
left join Device_Vehicle f on a.fk_vehicle=f.keyid
left join Device_Vehicle g on a.fk_trailer=g.keyid
left join Contract_Income_Contract h on h.Old_Contract_KeyId = a.keyid and h.Is_Delete = 0
where a.keyid is not null
uj5u.com熱心網友回復：
子查詢換成表連接，再試試
select a.*,c.name Contract_TypeName,d.ecc_orga_name UnitName,d.ecc_orga_name VehicleTeamName
,e.name SettlementName,f.Vehicle_Number VehicleNo,g.Vehicle_Number TrailerNo
,case when a.Contract_Status=10 then '初始創建'when a.Contract_Status=20 then '合同作廢'
when a.Contract_Status=30 then '審核完畢' end StatusName
case when a.Old_Contract_KeyId is null and a.Contract_Status = 30 and h.Old_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
from Contract_InCome_Contract a
left join Dic_Contract_Type c on a.Dic_Contract_Type=c.keyid
left join Dic_Organization d on a.PartyA=d.keyid
left join Dic_Customer_Trans e on a.PartyB=e.keyid
left join Device_Vehicle f on a.fk_vehicle=f.keyid
left join Device_Vehicle g on a.fk_trailer=g.keyid
left join Contract_Income_Contract h on h.Old_Contract_KeyId = a.keyid and h.Is_Delete = 0
where a.keyid is not null
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/63564.html
標籤：開發
上一篇：請教各位大佬執行插入陳述句時，如何生成 001 001001 001002 002 002001 002002這種編號，非常感謝!