我现在在写一个缺勤的SQL,两个表,userinfo表
checkinout表
我现在写到
select b.checkTime,a.userId,a.checkDate from (
select distinct userId,format(checktime,'yyyy-mm-dd') as checkDate from CHECKINOUT
where format(checktime,'yyyy-mm-dd') in
(
select format(checktime,'yyyy-mm-dd')as checktime
from CHECKINOUT
where checktime<CDate('2014/7/1') and checktime>=CDate('2014/6/1')
group by format(checktime,'yyyy-mm-dd')
having count(1)>20
)
) as a right join (
select format(checktime,'yyyy-mm-dd')as checktime
from CHECKINOUT
where checktime<CDate('2014/7/1') and checktime>=CDate('2014/6/1')
group by format(checktime,'yyyy-mm-dd')
having count(1)>20
) as b on b.checkTime=a.checkDate order by a.Userid
就是用left join做连接,连接前的是工作日期和***如下图
关键是如何显示这两个left join后没有的:比如:2014.6.5 3 2014.6.5
left join 后的效果:
本来应该有 2014.6.5 NULL NULL 但是没有,经过考虑觉得是其他记录连接干扰了
大神大神在哪里啊