比如下面的查询语句，35000行数据，要用5秒，如何优化？
s_e_l_e_c_t c.km5,dtkmyeb5,dtkmyeb26,dtkmyeb33,dtkmyeb34,dtkmyeb35,dtkmyeb36,dtkmyeb37,dtkmyeb38,dtkmyeb39,dtkmyeb40,dtkmyeb28,dtkmyeb30,dtkmyeb41,dtkmyeb42, iif(dtkmyeb33 is null,h.pzzb8,iif(dtkmyeb28 is not null,b.pzzb8,d.pzzb8)) as dtkmyeb8, iif(dtkmyeb33 is null,h.pzzb9,iif(dtkmyeb28 is not null,b.pzzb9,d.pzzb9)) as dtkmyeb9, iif(c.km5 = '借',iif(dtkmyeb30 is null,0,dtkmyeb30) + iif(dtkmyeb33 is null,iif(h.pzzb8 is null,0,h.pzzb8),iif(dtkmyeb28 is not null,iif(b.pzzb8 is null,0,b.pzzb8),iif(d.pzzb8 is null,0,d.pzzb8))) - iif(dtkmyeb33 is null,iif(h.pzzb9 is null,0,h.pzzb9),iif(dtkmyeb28 is not null,iif(b.pzzb9 is null,0,b.pzzb9),iif(d.pzzb9 is null,0,d.pzzb9))) , iif(dtkmyeb30 is null,0,dtkmyeb30) - iif(dtkmyeb33 is null,iif(h.pzzb8 is null,0,h.pzzb8),iif(dtkmyeb28 is not null,iif(b.pzzb8 is null,0,b.pzzb8),iif(d.pzzb8 is null,0,d.pzzb8))) + iif(dtkmyeb33 is null,iif(h.pzzb9 is null,0,h.pzzb9),iif(dtkmyeb28 is not null,iif(b.pzzb9 is null,0,b.pzzb9),iif(d.pzzb9 is null,0,d.pzzb9)))) as dtkmyeb10, dtkmyeb18,dtkmyeb20,dtkmyeb19,dtkmyeb22,dtkmyeb21,dtkmyeb23 from (((dtkmyeb a left join (s_e_l_e_c_t pzzb6,pzzb59 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb59 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb59 Union All s_e_l_e_c_t pzzb6,pzzb61 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb61 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb61 Union All s_e_l_e_c_t pzzb6,pzzb63 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb63 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb63 Union All s_e_l_e_c_t pzzb6,pzzb65 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb65 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb65 Union All s_e_l_e_c_t pzzb6,pzzb67 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb67 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb67 Union All s_e_l_e_c_t pzzb6,pzzb69 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb69 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb69 Union All s_e_l_e_c_t pzzb6,pzzb71 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb71 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb71 Union All s_e_l_e_c_t pzzb6,pzzb73 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb73 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb73 Union All s_e_l_e_c_t pzzb6,pzzb75 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb75 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb75 Union All s_e_l_e_c_t pzzb6,pzzb77 As pzzb100,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null and pzzb77 Is not null And cint(pzzb3) <= 1 group by pzzb6,pzzb77 ) b on b.pzzb6 = a.dtkmyeb5 And b.pzzb100 = a.dtkmyeb28) left join dtkm c on c.km7 = a.dtkmyeb5) left join (s_e_l_e_c_t pzzb6,sum(pzzb8) As pzzb8,sum(pzzb9) As pzzb9 from dtpzzb where pzzb142 Is not null And cint(pzzb3) <= 1 group by pzzb6) d on d.pzzb6 = a.dtkmyeb5) left join (s_e_l_e_c_t pzzb12 ,sum(pzzb8) as pzzb8,sum(pzzb9) as pzzb9 from dtpzzb where pzzb142 is not null and cint(pzzb3) <= 1 group by pzzb12) h on h.pzzb12 = a.dtkmyeb26
加好友 
发短信
Post By：2022/1/14 12:48:00 [显示全部帖子]
