select id,name
,正常=sum(case zt when '正常' then 1 else 0 end)
,迟到=sum(case zt when '迟到' then 1 else 0 end)
,早退=sum(case zt when '早退' then 1 else 0 end)
,矿工=sum(case zt when '矿工' then 1 else 0 end)
from a
group by id,name
,正常=sum(case zt when '正常' then 1 else 0 end)
,迟到=sum(case zt when '迟到' then 1 else 0 end)
,早退=sum(case zt when '早退' then 1 else 0 end)
,矿工=sum(case zt when '矿工' then 1 else 0 end)
from a
group by id,name
(select count(*) from a a1 where a1.id=aa.id and a1.name=aa.name and a1.zt='正常') as '正常',
(select count(*) from a a2 where a2.id=aa.id and a2.name=aa.name and a2.zt='迟到') as '迟到',
(select count(*) from a a3 where a3.id=aa.id and a3.name=aa.name and a3.zt='早退') as '早退',
(select count(*) from a a4 where a4.id=aa.id and a4.name=aa.name and a4.zt='矿工') as '矿工'
from a aa
group by id,name