declare @sql varchar(8000)
set @sql='select b.编号,b.姓名,b.打卡日期,'
select @sql=@sql+'打卡时间'+cast(b.id as varchar(10))+'=max(case when b.id='+cast(b.id as varchar(10))+' then 打卡时间 end),'
from
(select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a) b
group by b.id
set @sql=left(@sql,len(@sql)-1)
+' from (select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a) b
group by b.编号,b.姓名,b.打卡日期'
exec(@sql)
select b.编号,b.姓名,b.打卡日期,
打卡时间1=max(case when b.id=1 then 打卡时间 end),
打卡时间2=max(case when b.id=2 then 打卡时间 end),
打卡时间3=max(case when b.id=3 then 打卡时间 end)
from
(select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a) b
group by b.编号,b.姓名,b.打卡日期
select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a
问一下,这条语句是什么意思...?汉一下自己!
3 A01 A 2003-01-06 00:00:00.000 12:18
2 A01 A 2003-01-06 00:00:00.000 13:22
1 A01 A 2003-01-06 00:00:00.000 17:29
3 B01 B 2003-01-06 00:00:00.000 12:18
2 B01 B 2003-01-06 00:00:00.000 13:22
1 B01 B 2003-01-06 00:00:00.000 17:29
不明白为什么id会出现123这写值~~我以为会出现id=6!!!忘回答....
declare @sql varchar(8000)
set @sql='select b.编号,b.姓名,b.打卡日期,'
select @sql=@sql+'打卡时间'+cast(b.id as varchar(10))+'=max(case when b.id='+cast(b.id as varchar(10))+' then 打卡时间 end),'
from
(select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间<=a.打卡时间),* from #t a) b
group by b.id
set @sql=left(@sql,len(@sql)-1)
+' from (select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间<=a.打卡时间),* from #t a) b
group by b.编号,b.姓名,b.打卡日期'
exec(@sql)
declare @sql varchar(8000)
set @sql='select b.编号,b.姓名,b.打卡日期,'
select @sql=@sql+'打卡时间'+cast(b.id as varchar(10))+'=max(case when b.id='+cast(b.id as varchar(10))+' then 打卡时间 end),'
from
(select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a) b
group by b.id
set @sql=left(@sql,len(@sql)-1)
+' from (select id=(select count(*) from #t where 编号=a.编号 and 打卡日期=a.打卡日期 and 打卡时间>=a.打卡时间),* from #t a) b
group by b.编号,b.姓名,b.打卡日期'
exec(@sql)