Sql语句查询优化总结:
Date:2007.1.10
t_supervise_job_assign表如下：
id | building_id
1 | 101
2 | 101
3 | 102
4 | 103
5 | 103
为了取出（select count(*) from t_supervise_job_assign where building_id=a.id）同一building_id不同id的building_id数量，得到如下的表ct：
building_id | total
101 2
102 1
103 2
下面的查询用了子查询，通过building_id=a.id连外层的表，这样的查询效率超低，甚至查死数据库：
select distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date, (select count(*) from t_supervise_job_assign where building_id=a.id) status, a.create_user from t_building a , t_building_info d,t_monitor_plan e,t_monitor_record fwhere a.id=d.building_id AND e.f1_id=a.id AND e.stream_id=f.f1_stream_id AND a.modify_flag<>'D' AND a.building_name like '%顺德%' order by status,d.begin_date desc
修改后：
主要优化这条语句：
select count(*) from t_supervise_job_assign where building_id=a.id
优化后：
select t.building_id,count(t.id) as total from t_supervise_job_assign t group by t.building_id
整合为一个表：
再得到最终优化后的查询语句：
select distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date, ct.status, a.create_user from t_building a , (select t.building_id,count(t.id) as status from t_supervise_job_assign t group by t.building_id) ct, t_building_info d,t_monitor_plan e,t_monitor_record fwhere a.id=d.building_id AND e.f1_id=a.id AND ct.building_id(+)=a.id AND e.stream_id=f.f1_stream_id AND a.modify_flag<>'D' AND a.building_name like '%顺德%' order by status,d.begin_date desc
