下面这条语句如何优化:
SELECT rs.dept_id,
(SELECT COUNT(1) FROM club_member t_mem WHERE t_mem.dept_id=rs.dept_id) total,(SELECT COUNT(1) FROM club_member t_mem2 WHERE t_mem2.dept_id = rs.dept_id AND
t_mem2.reg_date > to_date('2012-09-09','yyyy-mm-dd') AND t_mem2.reg_date < to_date('2012-10-11','yyyy-mm-dd')) total_new,
round((SELECT COUNT(1) FROM club_member t_mem2 WHERE t_mem2.dept_id = rs.dept_id AND
t_mem2.reg_date > to_date('2012-09-09','yyyy-mm-dd') AND t_mem2.reg_date < to_date('2012-10-11','yyyy-mm-dd'))/
(SELECT decode(COUNT(1),0,1,COUNT(1)) FROM club_member t_mem WHERE t_mem.dept_id=rs.dept_id),4) new_percent
FROM rs_dept rs WHERE rs.upper_dept_id > 1 ORDER BY total DESC , dept_id ASC
SELECT rs.dept_id,
(SELECT COUNT(1) FROM club_member t_mem WHERE t_mem.dept_id=rs.dept_id) total,(SELECT COUNT(1) FROM club_member t_mem2 WHERE t_mem2.dept_id = rs.dept_id AND
t_mem2.reg_date > to_date('2012-09-09','yyyy-mm-dd') AND t_mem2.reg_date < to_date('2012-10-11','yyyy-mm-dd')) total_new,
round((SELECT COUNT(1) FROM club_member t_mem2 WHERE t_mem2.dept_id = rs.dept_id AND
t_mem2.reg_date > to_date('2012-09-09','yyyy-mm-dd') AND t_mem2.reg_date < to_date('2012-10-11','yyyy-mm-dd'))/
(SELECT decode(COUNT(1),0,1,COUNT(1)) FROM club_member t_mem WHERE t_mem.dept_id=rs.dept_id),4) new_percent
FROM rs_dept rs WHERE rs.upper_dept_id > 1 ORDER BY total DESC , dept_id ASC
FROM (SELECT a.dept_id d_id, SUM(1) total1,
SUM(DECODE(SIGN(b.reg_date - TO_DATE('2012-09-09','YYYY-MM-DD')),1,1,0) *
DECODE(SIGN(TO_DATE('2012-10-11','YYYY-MM-DD') - b.reg_date),1,1,0) ) total2
FROM dept_id a, club_member b
WHERE a.dept_id = b.dept_id
AND a.upper_dept_id > 1)
ORDER BY total1 DESC, d_id ASC