3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
请各位朋友帮忙看看，一条比较难的sql语句（等待...）
目前在做一个考勤系统，遇到了些困难：
我想做个统计，用一条sql语句实现。大概要实现这个功能：每个人在选定的时间内出席了多少天，其中有多少次是迟到，有多少次是早退，有多少次是出差等。
在sqlserver的一个表中有个表叫record，结构如下：
employeeId（numeric），occurred_date（datetime），eventType（numeric）该表没有主键。
其中employeeId代表员工编号，occurred_date在一天内完全可能刷卡多次，eventType代表他今天的出勤状态：1表正常出勤，2代表迟到，3代表早退，4代表出差。
由于具体的情况我只能用一条sql查询语句实现，期盼着各位朋友的帮忙！谢谢
我想做个统计，用一条sql语句实现。大概要实现这个功能：每个人在选定的时间内出席了多少天，其中有多少次是迟到，有多少次是早退，有多少次是出差等。
在sqlserver的一个表中有个表叫record，结构如下：
employeeId（numeric），occurred_date（datetime），eventType（numeric）该表没有主键。
其中employeeId代表员工编号，occurred_date在一天内完全可能刷卡多次，eventType代表他今天的出勤状态：1表正常出勤，2代表迟到，3代表早退，4代表出差。
由于具体的情况我只能用一条sql查询语句实现，期盼着各位朋友的帮忙！谢谢
...全文
请发表友善的回复…
发表回复
prcgolf 2006-01-22
up
ruicn 2006-01-21
select b.employeeId as 员工编号,
count(b.employeeId)as 出勤次数,
sum(b.正常)as 正常,
sum(b.迟到)as 迟到,
sum(b.早退)as 早退,
sum(b.出差)as 出差
from
(select employeeId,
sum(case when eventType=1 then 1 else 0 end)as 正常,
sum(case when eventType=2 then 1 else 0 end)as 迟到,
sum(case when eventType=3 then 1 else 0 end)as 早退,
sum(case when eventType=4 then 1 else 0 end)as 出差
from test
where occurred_date between '开始时间' and '结束时间'
group by employeeId,STR(DATEPART(year, occurred_date)) + STR(DATEPART(month, occurred_date))
+ STR(DATEPART(day, occurred_date))) as b
group by b.employeeId
$扫地僧$ 2006-01-20
--??
员工编号 出勤次数 迟到次数 早退次数 出差次数
1 2 0 0 0 --?? 1 有迟到啊!
2 2 1 0 0
3 1 1 0 0 --?? 3 没有迟到啊!?
4 1 0 1 0
5 1 0 0 1
还有你是不是一天内出勤多次算1次啊!?
员工编号 出勤次数 迟到次数 早退次数 出差次数
1 2 0 0 0 --?? 1 有迟到啊!
2 2 1 0 0
3 1 1 0 0 --?? 3 没有迟到啊!?
4 1 0 1 0
5 1 0 0 1
还有你是不是一天内出勤多次算1次啊!?
$扫地僧$ 2006-01-20
create table record
(
employeeId int,
occurred_date datetime,
eventType int
)
insert record select 1,'2005-11-11 8:30:20',1
insert record select 1,'2005-11-11 8:31:14',1
insert record select 1,'2005-11-11 9:10:40',2
insert record select 2,'2005-11-11 8:59:20',1
insert record select 2,'2005-11-11 8:24:20',1
insert record select 3,'2005-11-11 9:31:22',1
insert record select 1,'2005-11-12 8:44:52',1
insert record select 1,'2005-11-12 8:46:31',1
insert record select 2,'2005-11-12 9:29:52',2
insert record select 4,'2005-11-12 10:40:13',3
insert record select 5,'2005-11-12 8:00:43',4
select T1.employeeId,T1.occurred_date,
T1.天数,
T2.出勤数,
T2.迟到数,
T2.早退数,
T2.出差数
from
(select employeeId,convert(varchar(10),occurred_date,120) as occurred_date,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by employeeId,convert(varchar(10),occurred_date,120)) T2,
(select employeeId,occurred_date,count(*) as 天数 from
(select distinct employeeId,convert(varchar(10),occurred_date,120) as occurred_date from record) T
group by employeeId,occurred_date) T1
where T1.employeeId=T2.employeeId and T1.occurred_date=T2.occurred_date
--这样吧!
员工编号 日期 天数 出勤次数 迟到次数 早退次数 出差次数
1 2005-11-11 1 2 1 0 0
1 2005-11-12 1 2 0 0 0
2 2005-11-11 1 2 0 0 0
2 2005-11-12 1 0 1 0 0
3 2005-11-11 1 1 0 0 0
4 2005-11-12 1 0 0 1 0
5 2005-11-12 1 0 0 0 1
(
employeeId int,
occurred_date datetime,
eventType int
)
insert record select 1,'2005-11-11 8:30:20',1
insert record select 1,'2005-11-11 8:31:14',1
insert record select 1,'2005-11-11 9:10:40',2
insert record select 2,'2005-11-11 8:59:20',1
insert record select 2,'2005-11-11 8:24:20',1
insert record select 3,'2005-11-11 9:31:22',1
insert record select 1,'2005-11-12 8:44:52',1
insert record select 1,'2005-11-12 8:46:31',1
insert record select 2,'2005-11-12 9:29:52',2
insert record select 4,'2005-11-12 10:40:13',3
insert record select 5,'2005-11-12 8:00:43',4
select T1.employeeId,T1.occurred_date,
T1.天数,
T2.出勤数,
T2.迟到数,
T2.早退数,
T2.出差数
from
(select employeeId,convert(varchar(10),occurred_date,120) as occurred_date,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by employeeId,convert(varchar(10),occurred_date,120)) T2,
(select employeeId,occurred_date,count(*) as 天数 from
(select distinct employeeId,convert(varchar(10),occurred_date,120) as occurred_date from record) T
group by employeeId,occurred_date) T1
where T1.employeeId=T2.employeeId and T1.occurred_date=T2.occurred_date
--这样吧!
员工编号 日期 天数 出勤次数 迟到次数 早退次数 出差次数
1 2005-11-11 1 2 1 0 0
1 2005-11-12 1 2 0 0 0
2 2005-11-11 1 2 0 0 0
2 2005-11-12 1 0 1 0 0
3 2005-11-11 1 1 0 0 0
4 2005-11-12 1 0 0 1 0
5 2005-11-12 1 0 0 0 1
darklight2008 2006-01-20
好，数据和结果如下：
数据：
employeeId occurred_date eventType
1 2005-11-11 8:30:20 1
1 2005-11-11 8:31:14 1
1 2005-11-11 9:10:40 2
2 2005-11-11 8:59:20 1
2 2005-11-11 8:24:20 1
3 2005-11-11 9:31:22 1
1 2005-11-12 8:44:52 1
1 2005-11-12 8:46:31 1
2 2005-11-12 9:29:52 2
4 2005-11-12 10:40:13 3
5 2005-11-12 8:00:43 4
那么在2005-11-11到2005-11-12之间查询结果为：
员工编号 出勤次数 迟到次数 早退次数 出差次数
1 2 0 0 0
2 2 1 0 0
3 1 1 0 0
4 1 0 1 0
5 1 0 0 1
数据：
employeeId occurred_date eventType
1 2005-11-11 8:30:20 1
1 2005-11-11 8:31:14 1
1 2005-11-11 9:10:40 2
2 2005-11-11 8:59:20 1
2 2005-11-11 8:24:20 1
3 2005-11-11 9:31:22 1
1 2005-11-12 8:44:52 1
1 2005-11-12 8:46:31 1
2 2005-11-12 9:29:52 2
4 2005-11-12 10:40:13 3
5 2005-11-12 8:00:43 4
那么在2005-11-11到2005-11-12之间查询结果为：
员工编号 出勤次数 迟到次数 早退次数 出差次数
1 2 0 0 0
2 2 1 0 0
3 1 1 0 0
4 1 0 1 0
5 1 0 0 1
-狙击手- 2006-01-20
select
employeeId,
count(distinct convert(varchar(10),occurred_date,120)) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
employeeId,
count(distinct convert(varchar(10),occurred_date,120)) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
$扫地僧$ 2006-01-20
还不行的话!
就麻烦楼主把数据和想要的结果贴一些出来!!
就麻烦楼主把数据和想要的结果贴一些出来!!
$扫地僧$ 2006-01-20
Try:
select T1.employeeId,
T2.天数,
T1.出勤数,
T1.迟到数,
T1.早退数,
T1.出差数
from
(select employeeId,convert(varchar(10),occurred_date,120) as occurred_date,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by employeeId,convert(varchar(10),occurred_date,120)) T2,
(select employeeId,occurred_date,count(*) as 天数 from
(select distinct employeeId,convert(varchar(10),occurred_date,120) as occurred_date from record) T
group by employeeId,occurred_date) T1
where T1.employeeId=T2.employeeId and T1.occurred_date=T2.occurred_date
select T1.employeeId,
T2.天数,
T1.出勤数,
T1.迟到数,
T1.早退数,
T1.出差数
from
(select employeeId,convert(varchar(10),occurred_date,120) as occurred_date,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by employeeId,convert(varchar(10),occurred_date,120)) T2,
(select employeeId,occurred_date,count(*) as 天数 from
(select distinct employeeId,convert(varchar(10),occurred_date,120) as occurred_date from record) T
group by employeeId,occurred_date) T1
where T1.employeeId=T2.employeeId and T1.occurred_date=T2.occurred_date
小辉 2006-01-20
这样写应该可以吧
按照天group by
convert(varchar(10),occurred_date,101) 试一试
select
employeeId,
count(1) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,101)
按照天group by
convert(varchar(10),occurred_date,101) 试一试
select
employeeId,
count(1) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,101)
lw1a2 2006-01-20
如果早上8：00到公司，算是正常出勤：
insert into record values(1,'2005-1-1 8:00:00',1)
但是下午16：00就走了：
insert into record values(1,'2005-1-1 16:00:00',3)
这天到底算是正常还是早退呢？你同级的正常数，迟到数，早退数等，是数量还是天数呢？
insert into record values(1,'2005-1-1 8:00:00',1)
但是下午16：00就走了：
insert into record values(1,'2005-1-1 16:00:00',3)
这天到底算是正常还是早退呢？你同级的正常数，迟到数，早退数等，是数量还是天数呢？
lw1a2 2006-01-20
关键是“occurred_date在一天内完全可能刷卡多次”，:(
darklight2008 2006-01-20
to:happyflystone(仙林幽谷客) and scmail81(琳):
你们的方法统计的是刷卡的总次数，比如：我在10号刷了卡3次卡，但是出勤天数只能算是一天，你们的方法算出来的都是3天，希望继续关注，谢谢
你们的方法统计的是刷卡的总次数，比如：我在10号刷了卡3次卡，但是出勤天数只能算是一天，你们的方法算出来的都是3天，希望继续关注，谢谢
-狙击手- 2006-01-20
select
employeeId,
count(1) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
employeeId,
count(1) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
zlp321002 2006-01-20
select
employeeId,
convert(varchar(10),occurred_date,120) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
employeeId,
convert(varchar(10),occurred_date,120) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
where occurred_date between '开始时间' and '结束时间'
group by employeeId,convert(varchar(10),occurred_date,120)
$扫地僧$ 2006-01-20
Try:
select employeeId,
coutn(*) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by occurred_date
select employeeId,
coutn(*) as 天数,
sum(case when eventType=1 then 1 else 0 end ) as 出勤数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record
group by occurred_date
-狙击手- 2006-01-20
那就用上面的SQL做一个视图吧
darklight2008 2006-01-20
还有我觉得这样的功能如果专门用一个视图来做的话应该就要相对容易些了吧
trying....
trying....
$扫地僧$ 2006-01-20
select employeeId,
sum(出勤次数) as 出勤次数,
sum(迟到数) as 迟到数,
sum(早退数) as 早退数,
sum(出差数) as 出差数
from
(select employeeId,
count(distinct convert(varchar(10),occurred_date,120)) as 出勤次数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record where convert(varchar(10),occurred_date,120) between '2005-11-11' and '2005-11-12'
group by employeeId,convert(varchar(10),occurred_date,120)) T
group by employeeId
------
员工编号 出勤次数 迟到数 早退数 出差数
1 2 1 0 0
2 2 1 0 0
3 1 0 0 0
4 1 0 1 0
5 1 0 0 1
sum(出勤次数) as 出勤次数,
sum(迟到数) as 迟到数,
sum(早退数) as 早退数,
sum(出差数) as 出差数
from
(select employeeId,
count(distinct convert(varchar(10),occurred_date,120)) as 出勤次数,
sum(case when eventType=2 then 1 else 0 end ) as 迟到数,
sum(case when eventType=3 then 1 else 0 end ) as 早退数,
sum(case when eventType=4 then 1 else 0 end ) as 出差数
from record where convert(varchar(10),occurred_date,120) between '2005-11-11' and '2005-11-12'
group by employeeId,convert(varchar(10),occurred_date,120)) T
group by employeeId
------
员工编号 出勤次数 迟到数 早退数 出差数
1 2 1 0 0
2 2 1 0 0
3 1 0 0 0
4 1 0 1 0
5 1 0 0 1
darklight2008 2006-01-20
很感谢scmail81(琳)， lw1a2(一刀 Blog：http://spaces.msn.com/members/lw1a2/)，happyflystone(仙林幽谷客) 以及各位朋友的帮助，从你们的回复中我学到很多，以后定然多向你们学习的了。
to： happyflystone(仙林幽谷客)
我刚开始也是用union，在最外面一层用sum函数，里面用count函数，只可惜火候还浅，才弄sql没有好久，看了你的语句我才恍然大悟。
to： happyflystone(仙林幽谷客)
我刚开始也是用union，在最外面一层用sum函数，里面用count函数，只可惜火候还浅，才弄sql没有好久，看了你的语句我才恍然大悟。
SQL147 2006-01-20
:)