然後這樣即可:
select id,
遲到次數=sum(case when timec>'08:00:00' tand timec<'09:00:00' then 1 else 0 end),
曠工次數=sum(case when timec>'09:00:00' or timec is null then 1 else 0 end),
打卡次數=sum(case when timec is null then 0 else 1 end)
from
(
SELECT * FROM 工廠日歷 left join
(select id,
datec=convert(varchar(10),card_time,120),
timec=substring(convert(varchar,card_time,120),12,8)
from tablename
) a
on 工作日=DATEC
) b
group by ID