The select g.a ab121, g.p kcount, j.y ycount, j.z ysum from
(select f.a ab121, count (*) as pkcount from
(select e.a ab121 from ac01 a join ab55 b on a.a ab001=b.b ae601
Join ab57 d on da ab201=b.a ab201
The join ab58 e on e.a ab120 da ab120=
The join ac02 k on a.a ac001=k.a ac001 where k.a ac031 in (' 1 ', '2') and a.x nh005='6') f
The join group by f.a ab121) g
(select count (*) as yycount, sum (c.a. kc264) as zysum, e.a ab121
The from ac01 a join ab55 b on a.a ab001=b.b ae601
The join kc38 c on a.a ac001=c.a. ac001
Join ab57 d on da ab201=b.a ab201
The join ab58 e on e.a ab120 da ab120=
The join ac02 k on a.a ac001=k.a ac001
Where k.a ac031 in (' 1 ', '2') and a.x nh005='06'
And c.a. ae040 & gt;=? | | '00:00:00'
And c.a. ae040 & lt;=? | | '00:00:00'
Group by e.a ab121) j on g.a ab121=j.a ab121

CodePudding user response:

The execution plan posted and have a look

CodePudding user response:

1, stick the execution plan;
2, the table data in the said amount, said at least what data quantity is big,

CodePudding user response:

Ok, thank you, I wish a happy Dragon Boat Festival. Attach the execution plan such as holiday in the past, in for a look.

CodePudding user response:

SQL optimization is as follows:

 with TMP as 
(
The select e.a ab121, c.a. kc264, c.a. ae040 
The from ac01 a 
The join ab55 b on a.a ab001=b.b ae601 
Join ab57 d on da ab201=b.a ab201 
The join ab58 e on e.a ab120 da ab120=
The join ac02 k on a.a ac001=k.a ac001 
Where k.a ac031 in (' 1 ', '2') 
And a.x nh005='06' 
) 
The select aab121, 
Count (*) as pkcount, 
The sum (case when aae040 & gt;=? | | '00:00:00 and c.a. ae040 & lt;=? | | '00:00:00 then 1 else 0 end) as yycount, 
The sum (case when aae040 & gt;=? | | '00:00:00 and c.a. ae040 & lt;=? | | '00:00:00 then akc264 else 0 end) as zysum 
The from TMP 
Group by aab121 

CodePudding user response:

Table name and field name some casual, look at a headache ah,

CodePudding user response:

Thank you very much, give me a new way of thinking, learn, hope you all well,

CodePudding user response:

Last time speaking to you attach my SQL execution plan, please refer to, once again sincerely hope everyone work smoothly.

CodePudding user response:

reference 5 floor to find a nickname too difficult response:

table name and field name some casual, look at a headache ah,

Well, yes, to have a good improvement to improve