指纹考勤记录怎么转换成考勤表呀！

ztwz 2013-11-22 11:07:15

指纹库中有两张表一张是用户表字段如下
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弱到爆想了一下午硬没搞出来。

...全文

1577 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

开着拖拉机泡妞 2013-11-26

打赏
举报

这个就是行列转换问题。百度一下，帖子一大把。另外考勤这个东西，如果某天都没打卡，那么这个时间就不会出现在打卡记录表里面，所以写语句之前，考虑怎么把需要统计的时间范围之类的所有时间都给生成，防止缺少某天的日期

xxfvba 2013-11-26

打赏
举报

我的想法添加一张临时表，然后动态行专列 create table #t(code int, name varchar(20)) insert into #t select 2001, '张三' union all select 2002 , '李四' union all select 2003 , '王五' create table #tb(code int,checktime 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' select name,case when CONVERT(varchar,checktime,108)>'12:00' THEN '下午' else '上午' end as [day], CONVERT(varchar,checktime,108)as tim,CONVERT(varchar,checktime,111)as dat into #test from #tb a,#t b where a.code=b.code declare @s varchar(max) select @s=ISNULL(@s+',','')+'['+dat+']' from #test group by dat order by dat set @s='select * from #test pivot (max(tim) for dat in ('+@s+'))A order by name,day' exec (@s)

q465897859 2013-11-23

打赏
举报

人员表关联指纹表 case when 判断下就 ok了

ztwz 2013-11-23

打赏
举报

这个表可以从新设计，在导入数据的时候添加到更合理的表中。

Q315054403 2013-11-23

打赏
举报

设计问题，非SQL查询问题

---涛声依旧--- 2013-11-23

打赏
举报

要用存储过程来实现了

LongRui888 2013-11-23

打赏
举报

查询结果：

LongRui888 2013-11-23

打赏
举报

这样吗：



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