尽可能的限制条件
Where条件多了好啊，多了建索引的可能，也减少了结果集，尤其对于那种以查询结果做derived表的，更应该从中间就限制结果集。比如这个查询
1 select
2
3 t.person_id,
4
5 t.name,
6
7 t.class_id,
8
9 t.class_name,
10
11 t.grade_num,
12
13 t.cn,
14
15 t1.goodnum,
16
17 t2.infonum
18
19 from
20
21 (
22
23 select
24
25 bp.person_id,
26
27 bp.name,
28
29 bc.class_id,
30
31 bc.class_name,
32
33 bg.grade_num,
34
35 COUNT(distinct v.person_id) cn
36
37 from vir_extra_appraisal v,bas_person bp,bas_student b,bas_class bc,bas_grade bg
38
39 where v.bas_person_id=bp.person_id
40
41 and v.person_id=b.person_id
42
43 and b.class_id=bc.class_id
44
45 and bc.grade_id =bg.grade_id
46
47 and v.bas_person_id =12762
48
49 and v.appraisal_date >='2008-08-02 00:00:00'
50
51 and v.appraisal_date <='2008-09-03 23:59:59'
52
53 group by v.bas_person_id
54
55 )
56
57 t
58
59 left join
60
61 (
62
63 select
64
65 v1.bas_person_id,count(distinct v1.person_id)as goodnum
66
67 from vir_extra_appraisal v1
68
69 where v1.appraisal_type=8501001
70
71 and v1.appraisal_date >='2008-08-02 00:00:00'
72
73 and v1.appraisal_date <='2008-09-03 23:59:59'
74
75 group by v1.bas_person_id
76
77 )
78
79 t1 on (t1.bas_person_id=t.person_id )
80
81 left join
82
83 (
84
85 select
86
87 v2.bas_person_id,count(distinct v2.person_id)as infonum
88
89 from vir_extra_appraisal v2
90
91 where v2.appraisal_type=8501002
92
93 and v2.appraisal_date >='2008-08-02 00:00:00'
94
95 and v2.appraisal_date <='2008-09-03 23:59:59'
96
97 group by v2.bas_person_id
98
99 )
100
101 t2 on (t2.bas_person_id=t.person_id )
2
3 t.person_id,
4
5 t.name,
6
7 t.class_id,
8
9 t.class_name,
10
11 t.grade_num,
12
13 t.cn,
14
15 t1.goodnum,
16
17 t2.infonum
18
19 from
20
21 (
22
23 select
24
25 bp.person_id,
26
27 bp.name,
28
29 bc.class_id,
30
31 bc.class_name,
32
33 bg.grade_num,
34
35 COUNT(distinct v.person_id) cn
36
37 from vir_extra_appraisal v,bas_person bp,bas_student b,bas_class bc,bas_grade bg
38
39 where v.bas_person_id=bp.person_id
40
41 and v.person_id=b.person_id
42
43 and b.class_id=bc.class_id
44
45 and bc.grade_id =bg.grade_id
46
47 and v.bas_person_id =12762
48
49 and v.appraisal_date >='2008-08-02 00:00:00'
50
51 and v.appraisal_date <='2008-09-03 23:59:59'
52
53 group by v.bas_person_id
54
55 )
56
57 t
58
59 left join
60
61 (
62
63 select
64
65 v1.bas_person_id,count(distinct v1.person_id)as goodnum
66
67 from vir_extra_appraisal v1
68
69 where v1.appraisal_type=8501001
70
71 and v1.appraisal_date >='2008-08-02 00:00:00'
72
73 and v1.appraisal_date <='2008-09-03 23:59:59'
74
75 group by v1.bas_person_id
76
77 )
78
79 t1 on (t1.bas_person_id=t.person_id )
80
81 left join
82
83 (
84
85 select
86
87 v2.bas_person_id,count(distinct v2.person_id)as infonum
88
89 from vir_extra_appraisal v2
90
91 where v2.appraisal_type=8501002
92
93 and v2.appraisal_date >='2008-08-02 00:00:00'
94
95 and v2.appraisal_date <='2008-09-03 23:59:59'
96
97 group by v2.bas_person_id
98
99 )
100
101 t2 on (t2.bas_person_id=t.person_id )
可以看到，derived表t是限制了v.bas_person_id =12762，最终的结果集是只查一个人的情况，derived表t、t1是针对所有人做的汇总，而在和derived表t 做join的时候，给过滤掉了，最终只保留了v.bas_person_id =12762的记录，这是何苦呢?直接在t1 和 t2里过滤掉多好，于是在t1 和t2里分别加上条件person_id=12762。
