改成了 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
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