select T2.phone,T2.[Date]
from
(select phone,Count(*) as Cnt
from 表名
group by phone
having count(*)>=5
) T1
inner join
(select * from (
select phone,[date],(select count(*) from 表名 where phone=A.phone and [Date]<=A.[Date]) as 行号
from 表名 as a) TT where 行号=5) T2 on T1.phone=T2.phone
from
(select phone,Count(*) as Cnt
from 表名
group by phone
having count(*)>=5
) T1
inner join
(select * from (
select phone,[date],(select count(*) from 表名 where phone=A.phone and [Date]<=A.[Date]) as 行号
from 表名 as a) TT where 行号=5) T2 on T1.phone=T2.phone
select T2.phone,T2.CallDate
from
(select phone,Count(*) as Cnt
from 表名
group by phone
having count(*)>=5
) T1
inner join
(select * from (
select phone,CallDate,(select count(*) from 表名 where phone=A.phone and CallDate<=A.CallDate) as 行号
from 表名 as a) TT where 行号=5) T2 on T1.phone=T2.phone
(
phone int ,
date datetime
)insert into content select 111 ,'06-6-1'
union all select 222 ,'06-6-2'
union all select 111 ,'06-6-3'
union all select 111 ,'06-6-8'
union all select 111 ,'06-7-4'
union all select 222 ,'06-8-9'
union all select 111 ,'06-8-13'
union all select 222 ,'06-8-22'
union all select 222 ,'06-8-25'
union all select 222 ,'06-9-11'
union all select 333 ,'06-9-12'
union all select 111 ,'06-9-13'
union all select 333 ,'06-9-14'select * From content a
where exists (select 1 from content where phone =a.phone group by phone having count(1)>=5)
and date in (select top 1 date from content c where c.phone =a.phone
and date in (select top 5 date from content where c.phone =phone order by date ) order by date desc )
phone date
----------- ------------------------------------------------------
111 2006-08-13 00:00:00.000
222 2006-09-11 00:00:00.000(所影响的行数为 2 行)