3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
不知道怎么查询这样的结果!!!!!!!
有这样一些记录:
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
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
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
JL99000 2008-01-16
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
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
entironment 2008-01-15
都是正解啊，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
也出一个，效率不保证
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
wzy_love_sly 2008-01-15
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
kuangdp 2008-01-15
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-狙击手- 2008-01-15
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 Percodewzy_love_sly 2008-01-15
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
