日期:2014-05-18  浏览次数:20430 次

100分求一个sql语句
表结构很简单
content表只有2个字段,一个phone,一个calldate,phone记录电话号码,calldate记录打电话时间,举例说明下:
phone     date
111         06-6-1
222         06-6-2
111         06-6-3
111         06-6-8
111         06-7-4
222         06-8-9
111         06-8-13
222         06-8-22
222         06-8-25
222         06-9-11
333         06-9-12
111         06-9-13
333         06-9-14

需要统计出打电话大于5次的电话号码,和第五次的时间。注意,正好是第五次的时间!请问这样的sql如何写。
结果如下:
phone         calldate
111             06-8-13
222             06-9-11
不知道我叙述的是否清楚,今天随时在线,请大虾帮忙,已经解决,立刻给分!

------解决方案--------------------


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
------解决方案--------------------
create table content
(
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 行)