Join Vs select 结果集作列
有时候我们需要关联很多表统计：
select f1, f2, count(*) from a join b join c join d join e ……
如果结果集很小，只统计出几条数据来，那么可以换成这样写
Select f1, f2, (select count(*) from a join b ) from c……
比如下面的例子：
1 select t1.assignment_id assignmentId,t5.person_id,
2 t2.subject_name subjectName,
3 t3.dict_name assignType,
4 t1.assign_date assignDate,
5 t4.totalcount totalCount,
6 (select dict_name from bas_diction where dict_id = t5.assign_marks) as assignMarks,
7 (case t5.if_submit when 1 then '是' else '否' end) as ifSubmit,
8 t6.errorcount errorCount,
9 t6.nocheckCount,
10 case t1.online_assignment when 1 then '是' else '否' end as ifOnline,
11 (select dict_name from bas_diction where dict_id = t5.write_appraise) as writeAppraise
12 from hom_assignmentinfo t1,bas_subject t2,bas_diction t3,
13 (select t1.assignment_id,count(*) as totalcount from hom_assignmentinfo t1,hom_assignment t2
14 where t1.assignment_id = t2.hom_assignment_id
15 group by t1.assignment_id) t4,hom_assignment_appraise t5 left join
16 (select t1.assignment_id,t3.appraise_id,
17 sum(case t3.check_result when 3003001 then 0 else 1 end) as errorcount,
18 sum(case when t3.check_result=3003001 or t3.recheck_result=3003001 then 0 else 1 end)as nocheckCount
19 from hom_assignmentinfo t1,hom_check_assignment t3
20 where t1.assignment_id = t3.assignment_id
21 and t1.person_id='13042'
22 group by t1.assignment_id,t3.appraise_id) t6 on (t5.appraise_id=t6.appraise_id)
23 where t1.subject_id = t2.subject_id
24 and t1.assign_type = t3.dict_id
25 and t1.assignment_id = t4.assignment_id
26 and t1.assignment_id = t5.assignment_id
27 and not exists (select 1 from hom_assignmentinfo t11,hom_assignment_appraise t12
28 where t11.assignment_id=t12.assignment_id and
29 t11.online_assignment = 0 and
30 t12.person_id='13042' and
31 t11.subject_id = t1.subject_id and t11.assign_date > t1.assign_date)
32 and t1.assign_date is not null
33 and t5.person_id='13042'
34 order by t1.assign_date desc;
35
2 t2.subject_name subjectName,
3 t3.dict_name assignType,
4 t1.assign_date assignDate,
5 t4.totalcount totalCount,
6 (select dict_name from bas_diction where dict_id = t5.assign_marks) as assignMarks,
7 (case t5.if_submit when 1 then '是' else '否' end) as ifSubmit,
8 t6.errorcount errorCount,
9 t6.nocheckCount,
10 case t1.online_assignment when 1 then '是' else '否' end as ifOnline,
11 (select dict_name from bas_diction where dict_id = t5.write_appraise) as writeAppraise
12 from hom_assignmentinfo t1,bas_subject t2,bas_diction t3,
13 (select t1.assignment_id,count(*) as totalcount from hom_assignmentinfo t1,hom_assignment t2
14 where t1.assignment_id = t2.hom_assignment_id
15 group by t1.assignment_id) t4,hom_assignment_appraise t5 left join
16 (select t1.assignment_id,t3.appraise_id,
17 sum(case t3.check_result when 3003001 then 0 else 1 end) as errorcount,
18 sum(case when t3.check_result=3003001 or t3.recheck_result=3003001 then 0 else 1 end)as nocheckCount
19 from hom_assignmentinfo t1,hom_check_assignment t3
20 where t1.assignment_id = t3.assignment_id
21 and t1.person_id='13042'
22 group by t1.assignment_id,t3.appraise_id) t6 on (t5.appraise_id=t6.appraise_id)
23 where t1.subject_id = t2.subject_id
24 and t1.assign_type = t3.dict_id
25 and t1.assignment_id = t4.assignment_id
26 and t1.assignment_id = t5.assignment_id
27 and not exists (select 1 from hom_assignmentinfo t11,hom_assignment_appraise t12
28 where t11.assignment_id=t12.assignment_id and
29 t11.online_assignment = 0 and
30 t12.person_id='13042' and
31 t11.subject_id = t1.subject_id and t11.assign_date > t1.assign_date)
32 and t1.assign_date is not null
33 and t5.person_id='13042'
34 order by t1.assign_date desc;
35
改写成下面的，速度由0.625秒优化到: 0.032
1 select t1.assignment_id assignmentId,t5.person_id,
2 t2.subject_name subjectName,
3 t3.dict_name assignType,
4 t1.assign_date assignDate,
5 (select count(*) as totalcount from hom_assignmentinfo where assignment_id=t1.assignment_id) totalcnt,
6 (
7 select sum(case t3.check_result when 3003001 then 0 else 1 end)
8 from hom_check_assignment t3
9 where t1.assignment_id = t3.assignment_id
10 ) errorcount,
11 (
12 select sum(case when t3.check_result=3003001 or t3.recheck_result=3003001 then 0 else 1 end)as nocheckCount
13 from hom_check_assignment t3
14 where t1.assignment_id = t3.assignment_id
15 ) nocheckCount,
16 (select dict_name from bas_diction where dict_id = t5.assign_marks) as assignMarks,
17 (case t5.if_submit when 1 then '是' else '否' end) as ifSubmit,
18 case t1.online_assignment when 1 then '是' else '否' end as ifOnline,
19 (select dict_name from bas_diction where dict_id = t5.write_appraise) as writeAppraise
20 from hom_assignmentinfo t1,bas_subject t2,bas_diction t3, hom_assignment_appraise t5
21 where t1.subject_id = t2.subject_id
22 and t1.assign_type = t3.dict_id
23 and t1.assignment_id = t5.assignment_id
24 and not exists (select 1 from hom_assignmentinfo t11,hom_assignment_appraise t12
25 where t11.assignment_id=t12.assignment_id and
26 t11.online_assignment = 0 and
27 t12.person_id='13042' and
28 t11.subject_id = t1.subject_id and t11.assign_date > t1.assign_date)
29 and t1.assign_date is not null
30 and t5.person_id='13042'
31 order by t1.assign_date desc;
2 t2.subject_name subjectName,
3 t3.dict_name assignType,
4 t1.assign_date assignDate,
5 (select count(*) as totalcount from hom_assignmentinfo where assignment_id=t1.assignment_id) totalcnt,
6 (
7 select sum(case t3.check_result when 3003001 then 0 else 1 end)
8 from hom_check_assignment t3
9 where t1.assignment_id = t3.assignment_id
10 ) errorcount,
11 (
12 select sum(case when t3.check_result=3003001 or t3.recheck_result=3003001 then 0 else 1 end)as nocheckCount
13 from hom_check_assignment t3
14 where t1.assignment_id = t3.assignment_id
15 ) nocheckCount,
16 (select dict_name from bas_diction where dict_id = t5.assign_marks) as assignMarks,
17 (case t5.if_submit when 1 then '是' else '否' end) as ifSubmit,
18 case t1.online_assignment when 1 then '是' else '否' end as ifOnline,
19 (select dict_name from bas_diction where dict_id = t5.write_appraise) as writeAppraise
20 from hom_assignmentinfo t1,bas_subject t2,bas_diction t3, hom_assignment_appraise t5
21 where t1.subject_id = t2.subject_id
22 and t1.assign_type = t3.dict_id
23 and t1.assignment_id = t5.assignment_id
24 and not exists (select 1 from hom_assignmentinfo t11,hom_assignment_appraise t12
25 where t11.assignment_id=t12.assignment_id and
26 t11.online_assignment = 0 and
27 t12.person_id='13042' and
28 t11.subject_id = t1.subject_id and t11.assign_date > t1.assign_date)
29 and t1.assign_date is not null
30 and t5.person_id='13042'
31 order by t1.assign_date desc;
