楼主是不是一个这样的SQL:
select t1.*,t2.cd,t3.qj from (
tbl_renyuan t1 left join
(select department,name,count(*) cd
from tbl_kaoqin
where status=1 #迟到
and date='2017-1-1'
group by department,name) t2
on t1.department=t2.department and t1.name=t2.name)
left join
(select department,name,count(*) qj
from tbl_kaoqin b
where status=2 #请假
and date='2017-1-1'
group by department,name) t3
on t1.department=t3.department and t1.name=t3.name;
select dept as 部门,emp as 员工,
case
when a.state=1 then '请假'
when a.state=2 then '迟到'
END as 状态,
empDate as 日期
from
(select csdn2_1.*,csdn2_2.state,csdn2_2.empDate from csdn2_1 left JOIN csdn2_2 on csdn2_1.emp = csdn2_2.emp) a