通过连接想查找指定日期没有签到的人员(没有签到的人员没有对应的日期),但根据日期查找后,日期条件会过滤掉没有日期的人员(也就把每签到的过滤了)显示的是打卡。请问大牛该如何解决,谢谢!
select kq_user.num,
kq_user.name,
kq_day.day_time,
kq_day.arrival_time,
kq_user.deptName
from kq_user, kq_day
where kq_user.num = kq_day.userid(+)
and kq_day.day_time = '2010-9-20'
and kq_user.deptNo = '4'
select kq_user.num,
kq_user.name,
kq_day.day_time,
kq_day.arrival_time,
kq_user.deptName
from kq_user, kq_day
where kq_user.num = kq_day.userid(+)
and kq_day.day_time = '2010-9-20'
and kq_user.deptNo = '4'
kq_user.name,
kq_day.day_time,
kq_day.arrival_time,
kq_user.deptName
from kq_user, kq_day
where kq_user.num = kq_day.userid(+)
and (kq_day.day_time = '2010-9-20' or kq_day.day_time is null)
and kq_user.deptNo = '4'
还有别的日期限定吗?这样的话是把未打卡的显示出来了,但是不一定是当天的
select kq_user.num,
kq_user.name,
kq_day.day_time,
kq_day.arrival_time,
kq_user.deptName
from kq_user, kq_day
where kq_user.num = kq_day.userid(+)
and kq_day.day_time(+) = '2010-9-20'
and kq_user.deptNo = '4'
kq_day表作为子表,所有条件中kq_day后面都要加(+)
其实用left join 看起来清晰一些select kq_user.num,
kq_user.name,
kq_day.day_time,
kq_day.arrival_time,
kq_user.deptName
from kq_user
left join kq_day
on kq_day.userid = kq_user.num
and kq_day.day_time = '2010-9-20'
where kq_user.deptNo = '4'
没调试,也不知写得对不对