大家看看这个SQL语句怎么优化
如题，下面这个SQL语句，当数据很大时，就会卡死崩溃，请大家看看如何能优化。
- SQL code
Select areaid, areaname, 'houseid' as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee, FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr, FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug, FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece, FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As total From ( Select tmpMain.*, areaname, fzname,houproperties,project,sfyid, (Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */ From ( Select h.areaid, h.houseid, h.year, projectid, SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan, SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb, SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar, SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr, SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May, SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun, SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul, SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug, SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep, SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct, SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov, SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece, SUM(jfje) As sumyear /* All rent in the year */ From ( Select * From ( Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */ ) hh, project p /* That's cross join, try to get all projects */ Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */ or (hh.houproperties = '1' and projectid ='1002')) ) h Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4) Group By h.areaid, h.houseid, h.year, projectid ) tmpMain /* Here we got the main result */ Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */ Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */ Join area a On tmpMain.areaid = a.rowid /* For get the area's name */ ) tmpAll /* Here you can write some condition */ Order By areaid, houseid, year, projectid;
欢迎大家阅读《大家看看这个SQL语句如何优化》,跪求各位点评，by 搞代码
——解决方案——————–
贴出你的
explain select …
以供分析。
——解决方案——————–
这么长的sql代码啊，怎么不考虑键视图或存储过程啊
——解决方案——————–
子查询太多，相互之间join之后，需要扫描的行上亿了。
——解决方案——————–
先执行内层，在以结果执行外层，不知道可以不
- SQL code
Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1 /* That's cross join, try to get all years */ ) hh, project p /* That's cross join, try to get all projects */ Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */ or (hh.houproperties = '1' and projectid ='1002'))搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：大家看看这个SQL语句如何优化
