mysql标量子查询变成左连接_MYSQL count标量子查询改left join

SELECT homepageId, userId, homepagesummary, totalviews, totalleadpercents, totalappointments, homepagestatus, linkphone, imagepath, createtime, updatetime, recommendtime, sortcode, designerimagepath, isrecommend, ( SELECT count(*) FROM homepagexxxrelation WHERE homepageid=h.homepageid ANDgroundingstatus=0 )AStotalxxx, ( SELECT count(*) FROM homepagexxxrelation WHERE homepageid=h.homepageid ANDgroundingstatus=0 ANDCreatetime>=DATE_SUB(sysdate(),INTERVAL7DAY) )ASweektotalxxx FROM designerhomepageh WHERE 1=1andisrecommend=1orderbyweektotalxxxDESC, homepageidDESC; 优化后: SELECT h.homepageId, h.userId, h.homepagesummary, h.totalviews, h.totalleadpercents, h.totalappointments, h.homepagestatus, h.linkphone, h.imagepath, h.createtime, h.updatetime, h.recommendtime, h.sortcode, h.designerimagepath, h.isrecommend, ifnull(h2.cnt2,0)AStotalscheme, ifnull(h2.cnt1,0)ASweektotalxxx FROM designerhomepageh LEFTJOIN( SELECT Homepageid, sum(casewhenHP.Createtime>=DATE_SUB(sysdate(),INTERVAL7DAY)then1else0end)cnt1, count(*)cnt2 FROM homepagexxxrelationhp WHERE HP.groundingstatus=0 GROUPBY Homepageid )h2ONh2.homepageid=h.homepageid WHERE 1=1 andisrecommend=1 ORDERBY weektotalxxxDESC, homepageidDESC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29863023/viewspace-2675930/，如需转载，请注明出处，否则将追究法律责任。