with cte as
(
select '000019' CardId, cast('2012-5-15 0:00' as datetime) DataData, cast('2012-5-15 7:40' as datetime) DataTime,88 MachAddr union all
select '000019' ,'2012-5-15 0:00' ,'2012-5-15 13:52',88 union all
select '000019' ,'2012-5-15 0:00' ,'2012-5-15 18:00',88 union all
select '000021' ,'2012-5-15 0:00' ,'2012-5-15 7:40',78 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 8:05',78 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 12:18',88 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 14:08',88
),tempa as
(
select CardId ,DataData,DataTime,ROW_NUMBER() over (PARTITION by CardId order by DataTime) row from cte
),t as
(
select cardid,DataData,DataTime,row from tempa
union all
select cardid,DataData,null,row+1 from t a where Not exists (
select cardid,DataData,DataTime,row from tempa b where b.row = a.row + 1 and a.CardId = b.CardId
)
and a.row <4
)
select * from t order by CardId,row
(
select '000019' CardId, cast('2012-5-15 0:00' as datetime) DataData, cast('2012-5-15 7:40' as datetime) DataTime,88 MachAddr union all
select '000019' ,'2012-5-15 0:00' ,'2012-5-15 13:52',88 union all
select '000019' ,'2012-5-15 0:00' ,'2012-5-15 18:00',88 union all
select '000021' ,'2012-5-15 0:00' ,'2012-5-15 7:40',78 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 8:05',78 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 12:18',88 union all
select '000032' ,'2012-5-15 0:00' ,'2012-5-15 14:08',88
),tempa as
(
select CardId ,DataData,DataTime,ROW_NUMBER() over (PARTITION by CardId order by DataTime) row from cte
),t as
(
select cardid,DataData,DataTime,row from tempa
union all
select cardid,DataData,null,row+1 from t a where Not exists (
select cardid,DataData,DataTime,row from tempa b where b.row = a.row + 1 and a.CardId = b.CardId
)
and a.row <4
)
select * from t order by CardId,row