blogArticle 是BLOG 文章表, comment 是BLOG文章评论表,entry_id分别是blogArticle ,comment 的主键, sql 语句是找出最近更新的BLOG文章, 包括有评论的更新,l_upd_date是更新日期, 所以找出blogArticle , comment 的最大更新日期,然后倒序排列就可以了,怎么去优化这条SQL语句呢? select entry_id
from (select be.entry_id,
max(be.l_upd_date) L1,
max(bc.l_upd_date) L2
from blogArticle be
left join comment bc on be.entry_id =
bc.entry_id
group by be.entry_id)
order by decode(sign(L1 - L2), -1, L2, L1) desc
from (select be.entry_id,
max(be.l_upd_date) L1,
max(bc.l_upd_date) L2
from blogArticle be
left join comment bc on be.entry_id =
bc.entry_id
group by be.entry_id)
order by decode(sign(L1 - L2), -1, L2, L1) desc
那group by还有意义吗?select ... from ... be left join ... bc ... order by ...
就可以了
其次:可以尝试如下sql
SELECT *
FROM (
SELECT BE.ENTRY_ID,
GREATEST(MAX(BE.L_UPD_DATE),
MAX(BC.L_UPD_DATE)) AS L
FROM BLOGARTICLE BE
LEFT JOIN COMMENT BC ON BE.ENTRY_ID = BC.ENTRY_ID
GROUP BY BE.ENTRY_ID
)
ORDER BY L
max(bc.l_upd_date) L2 如果哪个表数据量大的话,先进行分组,然后在连接
SELECT *
FROM (
SELECT BE.ENTRY_ID,
DECODE(MAX(BC.L_UPD_DATE),
NULL,
MAX(BE.L_UPD_DATE),
GREATEST(MAX(BE.L_UPD_DATE),
MAX(BC.L_UPD_DATE))) AS L
FROM BLOGARTICLE BE
LEFT JOIN COMMENT BC ON BE.ENTRY_ID = BC.ENTRY_ID
GROUP BY BE.ENTRY_ID)
ORDER BY L