select t.brand_id, t.name, t.image_url,(MCCOUNT+BCCOUNT) scount from (select rownum,b.*,r.orderno, (SELECT count(*)
FROM MERCHANT_COUPON mc,merchant_coupon_brand mcb, merchant_brand mb
WHERE mc.merchant_coupon_id=mcb.merchant_coupon_id and
mcb.merchant_brand_id = mb.id
and mb.brand_id = b.brand_id
AND TO_DATE(MC.STR_DATE, 'yyyy-mm-dd') <=
TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')
AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <=
TO_DATE(MC.END_DATE, 'yyyy-mm-dd')) AS MCCOUNT,
(select count(*)
from MERCHANT_BRAND_COUPON MBC
where b.brand_id=mbc.brand_id
AND TO_DATE(MBC.STR_DATE, 'yyyy-mm-dd') <=
TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')
AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <=
TO_DATE(MBC.END_DATE, 'yyyy-mm-dd')) AS BCCOUNT
from brand b left join rank r on b.brand_id=r.rank_id
where b.type_id = 2
order by r.orderno) t
where rownum <= 3
order by scount desc
效率不高 大家帮会优化下呗! 谢了 Oracle性能优化
FROM MERCHANT_COUPON mc,merchant_coupon_brand mcb, merchant_brand mb
WHERE mc.merchant_coupon_id=mcb.merchant_coupon_id and
mcb.merchant_brand_id = mb.id
and mb.brand_id = b.brand_id
AND TO_DATE(MC.STR_DATE, 'yyyy-mm-dd') <=
TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')
AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <=
TO_DATE(MC.END_DATE, 'yyyy-mm-dd')) AS MCCOUNT,
(select count(*)
from MERCHANT_BRAND_COUPON MBC
where b.brand_id=mbc.brand_id
AND TO_DATE(MBC.STR_DATE, 'yyyy-mm-dd') <=
TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')
AND TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') <=
TO_DATE(MBC.END_DATE, 'yyyy-mm-dd')) AS BCCOUNT
from brand b left join rank r on b.brand_id=r.rank_id
where b.type_id = 2
order by r.orderno) t
where rownum <= 3
order by scount desc
效率不高 大家帮会优化下呗! 谢了 Oracle性能优化
2)LZ 把该语句执行后的执行计划和统计信息贴上来、让大家帮你分析、