34,590
社区成员
发帖
与我相关
我的任务
分享
----------------根据版主指示,分组,排序,找第一条-------------
with kq
as
(
select * from
(select ID,
convert(varchar(10),员工表.时间,120) as 时间,
打卡机号,
ROW_NUMBER() over(partition by ID,convert(varchar(10),时间,120),打卡机编号 order by ID) as new_index
from 打卡表) a
where a.new_index=1
)
------------------行列转换--------------
SELECT
xx.班组,
xx.姓名,
xx.ID,
SUM(CASE WHEN kq.打卡机编号=1 then 1 else 0 end) as 1号机,
SUM(CASE WHEN kq.打卡机编号=2 then 1 else 0 end) as 2号机
FROM
kq
INNER JOIN
员工表 as xx
ON
kq.ID=xx.ID
GROUP BY
xx.班组,xx.姓名,xx.ID
order by ID
----------------------------------------------------------------
-- Author :TravyLee(两情若是久长时,又岂在朝朝暮暮!)
-- Date :2013-03-11 13:19:25
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
-- Jul 9 2008 14:17:44
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null
drop table [a]
go
create table [a]
(
[员工ID] int,
[姓名] varchar(4),
[班组] int
)
insert [a]
select 1,'张三',1 union all
select 2,'李四',1 union all
select 3,'王二',2
--> 测试数据:[b]
if object_id('[b]') is not null
drop table [b]
go
create table [b]
(
[员工ID] int,
[打卡时间] datetime,
[打卡机号] int
)
insert [b]
select 1,'2012-2-1 8:00',1 union all
select 2,'2012-2-1 9:00',1 union all
select 1,'2012-2-1 9:00',1 union all
select 3,'2012-2-1 8:00',2
go
select
a.班组,
a.姓名,
sum(case when b.打卡机号=1 then 1 else 0 end) as [1号机打卡次数],
sum(case when b.打卡机号=2 then 1 else 0 end) as [2号机打卡次数],
sum(case when b.打卡机号=3 then 1 else 0 end) as [3号机打卡次数],
sum(case when b.打卡机号=4 then 1 else 0 end) as [4号机打卡次数]
from
a
inner join
b
on
a.[员工ID]=b.[员工ID]
group by
a.班组,
a.姓名
/*
班组 姓名 1号机打卡次数 2号机打卡次数 3号机打卡次数 4号机打卡次数
-------------------------------------------------------------------------------------------
1 李四 1 0 0 0
1 张三 2 0 0 0
2 王二 0 1 0 0
*/