求一sql语句，在线等

[flower1515]

有一张打卡时间记录表　tableA
表结构如下：
autoid employeeid first 　　　　second 　　　third
1 001 08:30(正常)　　　11:50(早退)　　13:50(迟到)
2　　　　　　 001 08:00(正常) 12:00（正常） 13:45(迟到)
3 002 09:30(迟到)　　　11:50(早退)　　13:50(迟到)
4　　　　　　 002 08:30(正常) 12:00（正常） 13:45(迟到)

我想查询出统计的效果(就是每一个员工对应的迟到、早退、正常总次数)：
employeeid normal late early
001 3 2 1
002 2 3 1
高人帮忙

[肥胖的柠檬]

select employeeid ,
SUM(case when patindex('%正常%',first)>0 then 1 else 0 end ) normal,
SUM(case when patindex('%早退%',first)>0 then 1 else 0 end ) late,
SUM(case when patindex('%迟到%',first)>0 then 1 else 0 end ) early
from(
select employeeid , first from tableA union all
select employeeid , second from tableA union all
select employeeid , third from tableA )a
group by employeeid

[yrwx001]

select A.employeeid, normal = (select count(1) from A B where B.kaoqing = 'normal' and B.employeeid = A.employeeid),late = (select count(1) from A B where B.kaoqing = 'late' and B.employeeid = A.employeeid),early = (select count(1) from A B where B.kaoqing = 'early' and B.employeeid = A.employeeid) from (select employeeid,(case when first <= '08:30' then 'normal' else 'late' end ) as kaoqing from table union all
select employeeid,(case when second < '12:00' then 'early ' else 'normal' end ) as kaoqing from table union all
select employeeid,(case when third <= '13:30' then 'normal ' else 'late' end ) as kaoqing from table)A

[leo_lesley]

create table tab(autoid int,employeeid varchar(10),first varchar(20),second varchar(20),third varchar(20))
insert tab
select 1,'001','08:30(正常)','11:50(早退)','13:50(迟到)'
union all select 2,'001','08:00(正常)','12:00（正常)','13:45(迟到)'
union all select 3,'002','09:30(迟到)','11:50(早退)','13:50(迟到)'
union all select 4,'002','08:30(正常)','12:00（正常）','13:45(迟到)'


select employeeid,
normal=sum(case when charindex('正常',first)>0 then 1 else 0 end)+
sum(case when charindex('正常',second)>0 then 1 else 0 end)+
sum(case when charindex('正常',third)>0 then 1 else 0 end),
late=sum(case when charindex('早退',first)>0 then 1 else 0 end)+
sum(case when charindex('早退',second)>0 then 1 else 0 end)+
sum(case when charindex('早退',third)>0 then 1 else 0 end),
early=sum(case when charindex('迟到',first)>0 then 1 else 0 end)+
sum(case when charindex('迟到',second)>0 then 1 else 0 end)+
sum(case when charindex('迟到',third)>0 then 1 else 0 end)
from tab
group by employeeid


drop table tab

[flower1515]

谢谢,后来我自己写出来了,跟第一个朋友写的差不多,给分了