导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

gt1984 2008-01-15 10:21:20
有这样一些记录:

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
...全文
73 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过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
回复
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
回复
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 Percode
回复
wzy_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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告