请教SQL优化

[smartlylife]

select m.prop, m.planid, m.ava_date from t_avail_rate m,

(select b.prop, b.planid from
(select t.prop, t.planid from t_plan_info t where t.prop in ('1050', '1052') and (t.rate_code = 'BAR' or t.rate_code = 'BARB') ) a
left join t_avail_rate b on (a.prop = b.prop and a.planid = b.planid)
where to_char(b.ava_date, 'yyyy-mm-dd') >= '2007-11-01' and to_char(b.ava_date, 'yyyy-mm-dd') <= '2007-11-15'
group by b.prop, b.planid having count(b.ava_date ) < 15
) n
where m.prop = n.prop and m.planid = n.planid
and to_char(m.ava_date, 'yyyy-mm-dd') >= '2007-11-01' and to_char(m.ava_date, 'yyyy-mm-dd') <= '2007-11-15' order by m.prop, m.planid, m.ava_date


t_plan_info表中prop的所有planid对t_avail_rate表查询，如果11-01到11-15号之内(一天只有一条)的数据少一天就找出来，就是having count(b.ava_date ) < 15这句的意思
最后的结果我只能得到m.prop, m.planid, m.ava_date，我最想得到的是如果prop与planid相同时，少了11-06这天的记录的话，就显示的时候补上这条的空数据，当然如果很费时，或不能实现我就在程序中实现，问题不大，prop in (这里可能会有很多)，我怕这里多了速度会慢，不知道上面的话句可不可以优化一下，谢谢！

[feihua317]

写得太乱,你把你的代码用

--标签括起来吧!

[smartlylife]

select m.prop, m.planid, m.ava_date from t_avail_rate m, 

(select b.prop, b.planid from 
       (select t.prop, t.planid from t_plan_info t where t.prop in ('1050', '1052') and (t.rate_code = 'BAR' or t.rate_code = 'BARB') ) a 
       left join t_avail_rate b on (a.prop = b.prop and a.planid = b.planid) 
where to_char(b.ava_date, 'yyyy-mm-dd') >= '2007-11-01' and to_char(b.ava_date, 'yyyy-mm-dd') <= '2007-11-15' 
group by b.prop, b.planid having count(b.ava_date ) < 15
) n
where m.prop = n.prop and m.planid = n.planid
and to_char(m.ava_date, 'yyyy-mm-dd') >= '2007-11-01' and to_char(m.ava_date, 'yyyy-mm-dd') <= '2007-11-15' order by m.prop, m.planid, m.ava_date

[smartlylife]

上面是一个SQL，多层嵌套才实现了我想要的功能