, e.n ame SettlementName, f.V ehicle_Number VehicleNo, g.V ehicle_Number TrailerNo
The case when a.C ontract_Status=10 then 'initial creation when a.C ontract_Status=20 then' contract '
The when a.C ontract_Status=30 then 'audit completed end StatusName
, case when a.O ld_Contract_KeyId is null
And a.C ontract_Status=30
And not the exists (select * from Contract_Income_Contract where Old_Contract_KeyId=a.k eyid and Is_Delete=0) then one else 0 end IsDifferAdjust
The from Contract_InCome_Contract a
Left the join Dic_Contract_Type c on a. d. ic_Contract_Type=c.k eyid
Left the join Dic_Organization d on Amy polumbo artyA=d.k eyid
Left the join Dic_Customer_Trans e on Amy polumbo artyB=e.k eyid
Left the join Device_Vehicle f on a.f k_vehicle=f.k eyid
Left the join Device_Vehicle g on a.f k_trailer=g. eyid
Where a.k eyid is not null
Red part significantly affect the query speed, the optimization method has better
CodePudding user response:
Sub queries into table connection, try again
Select a. *, citigroup ame Contract_TypeName, d.e cc_orga_name UnitName, d.e cc_orga_name VehicleTeamName
, e.n ame SettlementName, f.V ehicle_Number VehicleNo, g.V ehicle_Number TrailerNo
The case when a.C ontract_Status=10 then 'initial creation when a.C ontract_Status=20 then' contract '
The when a.C ontract_Status=30 then 'audit completed end StatusName
case when a.O ld_Contract_KeyId is null and a.C ontract_Status=30 and h.O ld_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
The from Contract_InCome_Contract a
Left the join Dic_Contract_Type c on a. d. ic_Contract_Type=c.k eyid
Left the join Dic_Organization d on Amy polumbo artyA=d.k eyid
Left the join Dic_Customer_Trans e on Amy polumbo artyB=e.k eyid
Left the join Device_Vehicle f on a.f k_vehicle=f.k eyid
Left the join Device_Vehicle g on a.f k_trailer=g. eyid
left join Contract_Income_Contract h on h.O ld_Contract_KeyId=a.k eyid and h.I s_Delete=0
Where a.k eyid is not null
CodePudding user response:
Sub queries into table connection, try again
Select a. *, citigroup ame Contract_TypeName, d.e cc_orga_name UnitName, d.e cc_orga_name VehicleTeamName
, e.n ame SettlementName, f.V ehicle_Number VehicleNo, g.V ehicle_Number TrailerNo
The case when a.C ontract_Status=10 then 'initial creation when a.C ontract_Status=20 then' contract '
The when a.C ontract_Status=30 then 'audit completed end StatusName
Case the when a.O ld_Contract_KeyId is null and a.C ontract_Status=30 and h.O ld_Contract_KeyId is null then 1 else 0 end IsDifferAdjust
The from Contract_InCome_Contract a
Left the join Dic_Contract_Type c on a. d. ic_Contract_Type=c.k eyid
Left the join Dic_Organization d on Amy polumbo artyA=d.k eyid
Left the join Dic_Customer_Trans e on Amy polumbo artyB=e.k eyid
Left the join Device_Vehicle f on a.f k_vehicle=f.k eyid
Left the join Device_Vehicle g on a.f k_trailer=g. eyid
Left the join Contract_Income_Contract h on h.O ld_Contract_KeyId=a.k eyid and h.I s_Delete=0
Where a.k eyid is not null