Question

sql 多表查询问题 left join on 查询结果 总是不正确

有四张表，base_teacher存放的是所有人员的信息，teacher_project_map存放的是有项目人的信息，teacher_reward_map 存放奖励人员的信息， teacher_paper_map存放的是发表文章人的信息，
想要得到 所有人员在各个表中：发表项目的次数，奖励次数，发表文章次数，如下效果：

代码如下：
select base_teacher.col_code,count(teacher_project_map.col_pname) ,count(teacher_reward_map.col_rname),
count(teacher_paper_map.col_pname),
left outer join teacher_project_map on base_teacher.col_code=teacher_project_map.col_code
left outer join teacher_reward_map on base_teacher.col_code=teacher_reward_map.col_codeleft outer join teacher_paper_map on base_teacher.col_code=teacher_paper_map.col_code group by base_teacher.col_code cont中的结果不对，如果是两个表则left outer join on 结果正确 ，请教大神指点为什么？

Answer 1

多个表的left outer join on,他是这样的先执行第一个left outer join on然后将得到的结果在与第二个left outer join on后的表做左联接，以此类推，所以得到的结果是不正确的。
下面是我写的sql你可以参考下：
select col_code, (select count(1) from teacher_project_map where teacher_project_map.col_code=base_teacher.col_code) as count1,
(select count(1) from teacher_reward_map where teacher_reward_map.col_code=base_teacher.col_code) as count2,
(select count(1) from teacher_paper_map where teacher_paper_map.col_code=base_teacher.col_code)
from base_teacher

追问

count(1) 是什么意思啊？
我试了把count(1)换成
count(teacher_project_map.col_pname) ,
count(teacher_reward_map.col_rname),
count(teacher_paper_map.col_pname),
结果好都还是0 不正确

追答

count(1)就是统计查询出来的行数和count(*)差不多，而count(teacher_project_map.col_pname)就是统计teacher_project_map.col_pname不是NULL的行数