日期:2014-05-17 浏览次数:20689 次
--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'