不知道怎么查询这样的结果!!!!!!!

[gt1984]

有这样一些记录:

Percode PressTimeIn PressTimeOut State
00000001 2008-01-05 08:05:06 4
00000002 2008-01-06 08:07:18 2008-01-05 17:07:18 3
00000001 2008-01-06 08:05:06 2008-01-06 15:05:06 2
. . . .
. . . .
. . . .

字段 PerCode 人事编号 PressTimeIn 进入时间 PressTimeOut 离开时间 State状态(4.缺勤 2.早退 3.正常出勤)

现在我想得到这样的结果:
想得到每个人的缺勤，早退，正常出勤的次数。

如:
人事编号 缺勤次数 早退次数 正常出勤
00000001 1 1
00000002 0 0 1

[wzy_love_sly]

create table tb (Percode varchar(10),PressTimeIn datetime,PressTimeOut datetime,State int)

insert into tb select '00000001','2008-01-05   08:05:06',null,4
insert into tb select '00000002','2008-01-06   08:07:18','2008-01-05   17:07:18 ',3
insert into tb select '00000001','2008-01-05   08:05:06','2008-01-06   15:05:06 ',2

select Percode,
sum(case when State=4 then 1 else 0 end)as '缺勤',
sum(case when State=2 then 1 else 0 end)as '早退',
sum(case when State=3 then 1 else 0 end)as '正常出勤'
from tb group by Percode

Percode 缺勤 早退 正常出勤
00000001 1 1 0
00000002 0 0 1

[-狙击手-]

select 人事编号= Percode,
缺勤次数= sum(case when state=4 then 1 else 0 end),
早退次数= sum(case when state=2 then 1 else 0 end),
正常出勤= sum(case when state=3 then 1 else 0 end)
from t
group by Percode

[kuangdp]

select Percode,sum(Case when state='4' then 1 else 0 end) as  缺勤次数,
                sum(Case when state='2' then 1 else 0 end) as   早退次数,
                sum(Case when state='3' then 1 else 0 end) as  正常出勤
from TB
GROUP BY Percode

[wzy_love_sly]

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum(case when State='''+ltrim(State)+''' then 1 else 0 end)as ['+case ltrim(State) when 4 then '缺勤' when 2 then '早退' when 3 then '正常出勤' end+']'
from (select distinct State from tb)a
exec('select Percode,'+@sql+' from tb group by Percode')

Percode 早退 正常出勤 缺勤
00000001 1 0 1
00000002 0 1 0

[entironment]

都是正解啊，jf
也出一个，效率不保证
select distinct percode,
(select count(*) from tb where percode=p.percode and state=4) as 缺勤,
(select count(*) from tb where percode=p.percode and state=3) as 正常,
(select count(*) from tb where percode=p.percode and state=2) as 早退
from tb as p

[JL99000]

if object_id('tb') is not null
drop table tb
go
create table tb (Percode varchar(10),PressTimeIn datetime,PressTimeOut datetime,State int)
insert into tb select '00000001','2008-01-05 08:05:06',null,4
insert into tb select '00000002','2008-01-06 08:07:18','2008-01-05 17:07:18 ',3
insert into tb select '00000001','2008-01-05 08:05:06','2008-01-06 15:05:06 ',2
select * from tb

select Percode as 人事编号,sum(case when State=4 then 1 else 0 end) as 缺勤次数,
sum(case when State=2 then 1 else 0 end) as 早退次数,
sum(case when State=3 then 1 else 0 end) as 正常出勤
from tb
group by Percode