一般左连接是左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null).
这是我写的一段SQL:
select s.name,s.classcode,sum(k.late),sum(k.miss),sum(k.leave) from students s
left outer join kaoqing k
on s.id=k.stuid
group by k.stuid 因为加了group by子句,所以右表中没有匹配的行,无法显示.
我的问题是怎么把右表中没有匹配的行也显示出来???
---------select s.name,s.classcode,isnull(sum(k.late),0) late,isnull(sum(k.miss),0) miss,isnull(sum(k.leave),0) leave from students s
left outer join kaoqing k
on s.id=k.stuid
group by k.stuid
这是我写的一段SQL:
select s.name,s.classcode,sum(k.late),sum(k.miss),sum(k.leave) from students s
left outer join kaoqing k
on s.id=k.stuid
group by k.stuid 因为加了group by子句,所以右表中没有匹配的行,无法显示.
我的问题是怎么把右表中没有匹配的行也显示出来???
---------select s.name,s.classcode,isnull(sum(k.late),0) late,isnull(sum(k.miss),0) miss,isnull(sum(k.leave),0) leave from students s
left outer join kaoqing k
on s.id=k.stuid
group by k.stuid
left outer join kaoqing k
on s.id=k.stuid
group by s.name,s.classcodeorselect s.name , s.classcode , isnull(k.late,0) late , isnull(k.miss,0) miss , isnull(k.leave,0) leave
from students s left outer join (select stuid , sum(late) late , sum(miss) miss , sum(leave) leave from kaoqing group by stuid) k
on s.id=k.stuid
group by s.name,s.classcode