--试试吧 declare @sql varchar(8000) set @sql='select 工号,日期'select @sql=@sql+',max(case xh when '+cast(c.xh as varchar(10))+' then 时间 end) [时间'+cast(c.xh as varchar(10))+'] ' from (select distinct xh=(select count(*)+1 from tb1 a where a.工号=tb1.工号 and a.日期=tb1.日期 and a.时间<tb1.时间) from tb1) c set @sql=@sql+',min(时间) [最早],max(时间) [最晚] from (select *,xh=(select count(*)+1 from tb1 a where a.工号=tb1.工号 and a.日期=tb1.日期 and a.时间<tb1.时间) from tb1) c ' +' group by 工号,日期 order by 工号,日期'exec(@sql)
if object_id(N'Attance',N'U') is not null drop table Attance create table Attance ( 工号 varchar(50), 卡号 nvarchar(50), 日期 nvarchar(50), 时间 nvarchar(50), 卡机 nvarchar(50) ) --测试数据 insert into Attance select 'H0020', '1479012666', '20101220', '1115', '2' union all select 'H0020', '1479012666' ,'20101220' ,'1119' ,'1' union all select 'H0018', '1482350722' ,'20101221' ,'1123' ,'2' union all select 'H0006', '1428906818' ,'20101220', '1124' ,'2' union all select 'H0006', '1428906818' ,'20101220' ,'1155' ,'2' union all select 'H0017' ,'1490152162' ,'20101220' ,'1125' ,'2' union all select 'H0017' ,'1490152162' ,'20101220', '1126' ,'2' union all select 'H0017' ,'1490152162' ,'20101222' ,'1129' ,'2' union all select 'H0017' ,'1490152162' ,'20101220' ,'1115' ,'2' select 工号,日期,max(最早) as '最早',max(最晚) as '最晚',max(时间1) as '时间1', max(时间2)as '时间2',max(时间3)as '时间3',max(时间4)as '时间4',max(时间5)as '时间5' from ( select * from (select 工号, 日期, 时间, '时间' + cast(NewNum as varchar(5)) as '时间排序', case when NewNum = 1 then 时间 end as '最早', case when LastNum = 1 then 时间 end as '最晚' from (select 工号, 日期, 时间, row_number() over(partition by 工号, 日期 order by 时间 asc) as NewNum, row_number() over(partition by 工号, 日期 order by 时间 desc) as LastNum from Attance) b) d pivot(max(时间) for 时间排序 in (时间1, 时间2, 时间3, 时间4, 时间5)) c ) e group by 工号, 日期
select -- * distinct 工号, 日期, max(最早) over(partition by 工号, 日期) as '最早', max(最晚) over(partition by 工号, 日期) as '最晚', max(时间1) over(partition by 工号, 日期) as '时间1', max(时间2) over(partition by 工号, 日期) as '时间2', max(时间3) over(partition by 工号, 日期) as '时间3', max(时间4) over(partition by 工号, 日期) as '时间4', max(时间5) over(partition by 工号, 日期) as '时间5' from (select 工号, 日期, 时间, '时间' + cast(NewNum as varchar(5)) as '时间排序', case when NewNum = 1 then 时间 end as '最早', case when LastNum = 1 then 时间 end as '最晚' from (select 工号, 日期, 时间, row_number() over(partition by 工号, 日期 order by 时间 asc) as NewNum, row_number() over(partition by 工号, 日期 order by 时间 desc) as LastNum from Attance) b) d pivot(max(时间) for 时间排序 in (时间1, 时间2, 时间3, 时间4, 时间5)) c
declare @sql varchar(8000)
set @sql='select 工号,日期'select @sql=@sql+',max(case xh when '+cast(c.xh as varchar(10))+' then 时间 end) [时间'+cast(c.xh as varchar(10))+'] ' from
(select distinct xh=(select count(*)+1 from tb1 a where a.工号=tb1.工号 and a.日期=tb1.日期 and a.时间<tb1.时间) from tb1) c
set @sql=@sql+',min(时间) [最早],max(时间) [最晚] from (select *,xh=(select count(*)+1 from tb1 a where a.工号=tb1.工号 and a.日期=tb1.日期 and a.时间<tb1.时间) from tb1) c '
+' group by 工号,日期 order by 工号,日期'exec(@sql)
if object_id(N'Attance',N'U') is not null
drop table Attance
create table Attance
(
工号 varchar(50),
卡号 nvarchar(50),
日期 nvarchar(50),
时间 nvarchar(50),
卡机 nvarchar(50)
)
--测试数据
insert into Attance
select 'H0020', '1479012666', '20101220', '1115', '2' union all
select 'H0020', '1479012666' ,'20101220' ,'1119' ,'1' union all
select 'H0018', '1482350722' ,'20101221' ,'1123' ,'2' union all
select 'H0006', '1428906818' ,'20101220', '1124' ,'2' union all
select 'H0006', '1428906818' ,'20101220' ,'1155' ,'2' union all
select 'H0017' ,'1490152162' ,'20101220' ,'1125' ,'2' union all
select 'H0017' ,'1490152162' ,'20101220', '1126' ,'2' union all
select 'H0017' ,'1490152162' ,'20101222' ,'1129' ,'2' union all
select 'H0017' ,'1490152162' ,'20101220' ,'1115' ,'2' select 工号,日期,max(最早) as '最早',max(最晚) as '最晚',max(时间1) as '时间1',
max(时间2)as '时间2',max(时间3)as '时间3',max(时间4)as '时间4',max(时间5)as '时间5'
from (
select *
from (select 工号,
日期,
时间,
'时间' + cast(NewNum as varchar(5)) as '时间排序',
case
when NewNum = 1 then
时间
end as '最早',
case
when LastNum = 1 then
时间
end as '最晚'
from (select 工号,
日期,
时间,
row_number() over(partition by 工号, 日期 order by 时间 asc) as NewNum,
row_number() over(partition by 工号, 日期 order by 时间 desc) as LastNum
from Attance) b) d pivot(max(时间) for 时间排序 in (时间1, 时间2, 时间3, 时间4, 时间5)) c
) e
group by 工号, 日期
select
-- * distinct 工号,
日期,
max(最早) over(partition by 工号, 日期) as '最早',
max(最晚) over(partition by 工号, 日期) as '最晚',
max(时间1) over(partition by 工号, 日期) as '时间1',
max(时间2) over(partition by 工号, 日期) as '时间2',
max(时间3) over(partition by 工号, 日期) as '时间3',
max(时间4) over(partition by 工号, 日期) as '时间4',
max(时间5) over(partition by 工号, 日期) as '时间5' from (select 工号,
日期,
时间,
'时间' + cast(NewNum as varchar(5)) as '时间排序',
case
when NewNum = 1 then
时间
end as '最早',
case
when LastNum = 1 then
时间
end as '最晚'
from (select 工号,
日期,
时间,
row_number() over(partition by 工号, 日期 order by 时间 asc) as NewNum,
row_number() over(partition by 工号, 日期 order by 时间 desc) as LastNum
from Attance) b) d pivot(max(时间) for 时间排序 in (时间1, 时间2, 时间3, 时间4, 时间5)) c