select id,
sum(迟到次数) 迟到次数,
sum(迟到分钟) 迟到分钟,
sum(早退次数) 早退次数,
sum(早退分钟) 早退分钟,
sum(矿工次数) 矿工次数 from
(select id,
case when 员工上班时间1 > 上班时间1 then 1 else 0 end as 迟到次数,
case when 员工上班时间1 > 上班时间1 then 员工上班时间1 -上班时间1 else 0 end as 迟到分钟,
case when 员工下班时间1 < 下班时间1 then 1 else 0 end as 早退次数,
case when 员工下班时间1 < 下班时间1 then 下班时间1 -员工下班时间1 else 0 end as 早退分钟,
case when (员工上班时间1 is null) or (员工下班时间1 is null) then 1 else 0 end as 矿工次数
union all
select id,
case when 员工上班时间2 > 上班时间2 then 1 else 0 end as 迟到次数,
case when 员工上班时间2 > 上班时间2 then 员工上班时间2 -上班时间2 else 0 end as 迟到分钟,
case when 员工下班时间2 < 下班时间2 then 1 else 0 end as 早退次数,
case when 员工下班时间2 < 下班时间2 then 下班时间2 - 员工下班时间2 else 0 end as 早退分钟,
case when (员工上班时间2 is null) or (员工下班时间2 is null) then 1 else 0 end as 矿工次数
union all
......)
group by id
sum(迟到次数) 迟到次数,
sum(迟到分钟) 迟到分钟,
sum(早退次数) 早退次数,
sum(早退分钟) 早退分钟,
sum(矿工次数) 矿工次数 from
(select id,
case when 员工上班时间1 > 上班时间1 then 1 else 0 end as 迟到次数,
case when 员工上班时间1 > 上班时间1 then 员工上班时间1 -上班时间1 else 0 end as 迟到分钟,
case when 员工下班时间1 < 下班时间1 then 1 else 0 end as 早退次数,
case when 员工下班时间1 < 下班时间1 then 下班时间1 -员工下班时间1 else 0 end as 早退分钟,
case when (员工上班时间1 is null) or (员工下班时间1 is null) then 1 else 0 end as 矿工次数
union all
select id,
case when 员工上班时间2 > 上班时间2 then 1 else 0 end as 迟到次数,
case when 员工上班时间2 > 上班时间2 then 员工上班时间2 -上班时间2 else 0 end as 迟到分钟,
case when 员工下班时间2 < 下班时间2 then 1 else 0 end as 早退次数,
case when 员工下班时间2 < 下班时间2 then 下班时间2 - 员工下班时间2 else 0 end as 早退分钟,
case when (员工上班时间2 is null) or (员工下班时间2 is null) then 1 else 0 end as 矿工次数
union all
......)
group by id
select id,sum(case when 员工上班时间1>上班时间1 then 1 else 0 end)+sum(case when 员工上班时间2>上班时间2 then 1 else 0 end),
sum(case when 员工上班时间1>上班时间1 then datediff(mi,上班时间1,员工上班时间1) else 0 end)+sum(case when 员工上班时间2>上班时间2 then datediff(mi,上班时间2,员工上班时间2) else 0 end),
sum(case when 下班时间1>员工下班时间1 then 1 else 0 end)+sum(case when 下班时间2>员工下班时间2 then 1 else 0 end),
sum(case when 下班时间1>员工下班时间1 then datediff(mi,员工下班时间1,下班时间1) else 0 end)+sum(case when 下班时间2>员工下班时间2 then datediff(mi,员工下班时间2,下班时间2) else 0 end),
sum(case when 员工上班时间1 is null then 1 else 0 end)+sum(case when 员工上班时间2 is null then 1 else 0 end)+sum(case when 员工下班时间1 is null then 1 else 0 end)+sum(case when 员工下班时间2 is null then 1 else 0 end)
from 表1 group by id