指纹考勤记录怎么转换成考勤表呀！
指纹库中有两张表一张是用户表字段如下
code name
2001 张三
2002 李四
2003 王五
.....
一张是指纹打卡记录表字段如下
编号打卡时间
2001 2013/7/15 7:42
2001 2013/7/15 18:00
2002 2013/7/16 7:45
2002 2013/7/16 18:03
2003 2013/7/17 7:32
2001 2013/7/22 7:49
2003 2013/7/22 18:07
.....
现在我想把两张表转换成考勤表如下
请问我要这sql要怎么写？sql弱到爆想了一下午硬没搞出来。
考勤 打卡
[解决办法]
这样吗：
--drop table t
--drop table tb
--go
create table t(code int, name varchar(20))
insert into t
select 2001, '张三'
union all select 2002 , '李四'
union all select 2003 , '王五'
create table tb(编号 int,打卡时间 datetime)
insert into tb
select 2001 ,'2013/7/15 7:42'
union all select 2001, '2013/7/15 18:00'
union all select 2002, '2013/7/16 7:45'
union all select 2002, '2013/7/16 18:03'
union all select 2003, '2013/7/17 7:32'
union all select 2001, '2013/7/22 7:49'
union all select 2003, '2013/7/22 18:07'
go
select t.name,tt.v,
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='01'
then convert(varchar(5),打卡时间,114)
else null
end) as '01',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='02'
then convert(varchar(5),打卡时间,114)
else null
end) as '02',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='03'
then convert(varchar(5),打卡时间,114)
else null
end) as '03',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='04'
then convert(varchar(5),打卡时间,114)
else null
end) as '04',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='05'
then convert(varchar(5),打卡时间,114)
else null
end) as '05',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='06'
then convert(varchar(5),打卡时间,114)
else null
end) as '06',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='07'
then convert(varchar(5),打卡时间,114)
else null
end) as '07',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='08'
then convert(varchar(5),打卡时间,114)
else null
end) as '08',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='09'
then convert(varchar(5),打卡时间,114)
else null
end) as '09',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='10'
then convert(varchar(5),打卡时间,114)
else null
end) as '10',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='11'
then convert(varchar(5),打卡时间,114)
else null
end) as '11',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='12'
then convert(varchar(5),打卡时间,114)
else null
end) as '12',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='13'
then convert(varchar(5),打卡时间,114)
else null
end) as '13',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='14'
then convert(varchar(5),打卡时间,114)
else null
end) as '14',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='15'
then convert(varchar(5),打卡时间,114)
else null
end) as '15',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='16'
then convert(varchar(5),打卡时间,114)
else null
end) as '16',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='17'
then convert(varchar(5),打卡时间,114)
else null
end) as '17',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='18'
then convert(varchar(5),打卡时间,114)
else null
end) as '18',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='19'
then convert(varchar(5),打卡时间,114)
else null
end) as '19',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='20'
then convert(varchar(5),打卡时间,114)
else null
end) as '20',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='21'
then convert(varchar(5),打卡时间,114)
else null
end) as '21',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='22'
then convert(varchar(5),打卡时间,114)
else null
end) as '22',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='23'
then convert(varchar(5),打卡时间,114)
else null
end) as '23',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='24'
then convert(varchar(5),打卡时间,114)
else null
end) as '24',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='25'
then convert(varchar(5),打卡时间,114)
else null
end) as '25',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='26'
then convert(varchar(5),打卡时间,114)
else null
end) as '26',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='27'
then convert(varchar(5),打卡时间,114)
else null
end) as '27',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='28'
then convert(varchar(5),打卡时间,114)
else null
end) as '28',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='29'
then convert(varchar(5),打卡时间,114)
else null
end) as '29',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='30'
then convert(varchar(5),打卡时间,114)
else null
end) as '30',
max(case when substring(CONVERT(varchar(10),打卡时间,120),9,2)='31'
then convert(varchar(5),打卡时间,114)
else null
end) as '31'
from t
inner join
(
select '上午' v,'00:00:00' as min_v,'12:00:00' as max_v
union all select '下午','12:00:00', '00:00:00'
)tt
on 1 = 1
left join tb
on t.code = tb.编号
and CONVERT(varchar(8),打卡时间,114) >= tt.min_v
and CONVERT(varchar(8),打卡时间,114) < tt.max_v
group by t.code,t.name,tt.v
