难题：怎样每天抽取一条记录并分类统计

Question

有两张表：
一、员工信息表：
   员工ID  姓名  班组
    1     张三   1
    2     李四   1
    3     王二   2
    ...二、打卡记录表，存放员工打卡记录，共有4个不同类型的打卡机
   员工ID  打卡时间           打卡机号
     1    2012-2-1 8:00      1
     2    2012-2-1 9:00      1
     1    2012-2-1 9:00      1
     3    2012-2-1 8:00      2
     ...
这里有个难题，每人每天在某种型号的打卡机上打卡只能算一次，要求将某段时间内的打卡记录统计成这样：
    班组    员工姓名    1号机打卡次数   2号机打卡次数  3号机打卡次数   4号机打卡次数
     1      张三           ..            ..           ..           ..
     1      李四           ..            ..           ..           ..
     2      王二           ..            ..           ..           ..
     ...分不多不好意思，纠结了很久实在想不出来！

Answer 1

----------------------------------------------------------------
-- 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
*/

Answer 2

搞定了，ROW_NUMBER() + 行列转换
贴出来给朋友们参考。若有错误请指正
谢谢版主！----------------根据版主指示，分组，排序，找第一条-------------
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