子查询优化

[tjzero_sapce]

子查询太影响效率了，有大神能帮忙看看么？可以怎么优化？

SELECT 
  dw.NAME AS dwname,
  dzz.NAME AS dzzname,
  t.* 
FROM
  tbl_i_dzz dw 
  LEFT JOIN
  (SELECT 
    izbhd.tfid tfid,
    izbhd.hdsj,
    (YEAR(CURDATE()) - YEAR(izbhd.hdsj)) * 12 - MONTH(izbhd.hdsj) + MONTH(CURDATE()) AS result,
    izbhd.dzzid,    izbhd.hdyt,    izbhd.hddd,    izbhd.hdxxId,    izbhd.zcr,    izbhd.yddy,    izbhd.sddy,    izbhd.zw,
    izbhd.tplj,    izbhd.yplj,    izbhd.zttplj,    izbhd.sfpl,    izbhd.pls,    izbhd.dzs,    izbhd.scs,    izbhd.lll,    izbhd.yddyrs,
    izbhd.sddyrs,    izbhd.dwid,    izbhd.deleteFlag , izbhd.createDT 
  FROM
    tbl_i_zbhd izbhd 
  WHERE izbhd.tfid IN 
    (SELECT 
      MAX(tfid) 
    FROM
      tbl_i_zbhd 
    WHERE hdsj IN 
      (SELECT 
        MAX(hdsj) 
      FROM
        tbl_i_zbhd 
        WHERE DeleteFlag='0'
      GROUP BY 
      <isEqual property="DW" compareValue="1">
      	dwid
      </isEqual>
      <isEqual property="DW" compareValue="0">
      dzzid
      </isEqual>
      )   and DeleteFlag='0'
    GROUP BY 
    <isEqual property="DW" compareValue="1">
      	dwid
      </isEqual>
      <isEqual property="DW" compareValue="0">
      dzzid
      </isEqual>
    )
    AND DeleteFlag='0'
    ) t 
  ON  dw.TFID = t.dwid AND t.deleteFlag = '0'
  LEFT JOIN
  tbl_i_dzz dzz 
  ON t.dzzid = dzz.TFID 
WHERE dw.DeleteFlag='0' 
   AND getdzzlevel(dw.allpid,';')=2
<isNotEmpty prepend="and" property="dwid">
  dw.tfid=#dwid# 
</isNotEmpty>
<isNotEmpty prepend="and" property="dwname">
  dw.NAME LIKE '%$dwname$%' 
</isNotEmpty>
<isNotEmpty prepend="and" property="dzzname">
  dzz.NAME LIKE '%$dzzname$%' 
</isNotEmpty>
<isNotEmpty prepend="and" property="zw">
  t.zw LIKE '%$zw$%' 
</isNotEmpty>
<isNotEmpty prepend="Order By" property="order">
	$order$
</isNotEmpty>

[tjzero_sapce]

引用 1 楼 JenMinZhang 的回复:

能帮忙写下你改后的代码么？

[tjzero_sapce]

改成了 inner join 效率提升了五十倍 还有更好的写法么？

SELECT
	dw. NAME AS dwname,
	dzz. NAME AS dzzname,
	t.*
FROM
	tbl_i_dzz dw
LEFT JOIN (
	SELECT
		izbhd.tfid tfid,
		izbhd.hdsj,
		(
			YEAR (CURDATE()) - YEAR (izbhd.hdsj)
		) * 12 - MONTH (izbhd.hdsj) + MONTH (CURDATE()) AS result,
		izbhd.dzzid,		izbhd.hdyt,		izbhd.hddd,		izbhd.hdxxId,		izbhd.zcr,		izbhd.yddy,		izbhd.sddy,
		izbhd.zw,		izbhd.tplj,		izbhd.yplj,		izbhd.zttplj,		izbhd.sfpl,
		izbhd.pls,		izbhd.dzs,		izbhd.scs,		izbhd.lll,
		izbhd.yddyrs,		izbhd.sddyrs,		izbhd.dwid,		izbhd.deleteFlag,		izbhd.createDT
	FROM
		tbl_i_zbhd izbhd
	INNER JOIN 
	(
			SELECT
				MAX(tfid) as tfid
			FROM
				tbl_i_zbhd
			INNER JOIN
			 (
					SELECT
						MAX(hdsj) as hdsj
					FROM
						tbl_i_zbhd
					WHERE
						DeleteFlag = '0'
					GROUP BY
						dwid 
				) as a on a.hdsj = tbl_i_zbhd.hdsj
			AND tbl_i_zbhd.DeleteFlag = '0'
			GROUP BY
				tbl_i_zbhd.dwid
		) as b on b.tfid = izbhd.tfid 
	AND izbhd.DeleteFlag = '0'
) t ON dw.TFID = t.dwid
AND t.deleteFlag = '0'
LEFT JOIN tbl_i_dzz dzz ON t.dzzid = dzz.TFID
WHERE
	dw.DeleteFlag = '0'
AND getdzzlevel (dw.allpid, ';') = 2 

[知道就是你]