create table AAAA
(ApplyDate date, name varchar(8), CardID varchar(25))

insert into AAAA
select '2007-03-08', '张三', '310223197011082030' union all
select '2007-03-07', '张三', '310223197011082030' union all
select '2007-03-06', '张三', '310223197011082030' union all
select '2006-03-05', '张三', '310223197011082030' union all
select '2006-03-04', '张三', '310223197011082030' union all
select '2007-03-05', '李四', '310223197011082031'


with t as
(select * from AAAA
where ApplyDate between (select dateadd(d,-180,max(ApplyDate)) from AAAA)
and (select max(ApplyDate) from AAAA)
)
select * from t where name in 
(select name from t group by name having count(*)>=3)

ApplyDate  name     CardID
---------- -------- -------------------------
2007-03-08 张三       310223197011082030
2007-03-07 张三       310223197011082030
2007-03-06 张三       310223197011082030

------解决方案--------------------
/*
这样一个表
AAAA
字段如下

ApplyDate name CardID
2007-03-08 张三 310223197011082030
2007-03-07 张三 310223197011082030
2007-03-06 张三 310223197011082030
2006-03-05 张三 310223197011082030
2006-03-04 张三 310223197011082030
2007-03-05 李四 310223197011082031

我想统计出,以最新的一个日期开始往前算180天,
某个人报名次数超过3次的,在这180天内的，所有数据。
按上面得出结果是
2007-03-08 张三 310223197011082030
2007-03-07 张三 310223197011082030
2007-03-06 张三 310223197011082030
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
ApplyDate datetime,
name varchar(10),
CardID varchar(30)
)
go
insert tbl
select '2007-03-08','张三','310223197011082030' union all
select '2007-03-07','张三','310223197011082030' union all
select '2007-03-06','张三','310223197011082030' union all
select '2006-03-05','张三','310223197011082030' union all
select '2006-03-04','张三','310223197011082030' union all
select '2007-03-05','李四','310223197011082031'


;with T
as
(
select * from tbl
where ApplyDate between (select dateadd(d,-180,max(ApplyDate)) from tbl)
and (select max(ApplyDate) from tbl)
)
select *from T
where name in
(select name from tbl group by name having COUNT(name)>=3)


/*
ApplyDate	name	CardID
2007-03-08 00:00:00.000	张三	310223197011082030
2007-03-07 00:00:00.000	张三	310223197011082030
2007-03-06 00:00:00.000	张三	310223197011082030
*/

------解决方案--------------------
那楼主都"以最新的一个日期"的确切定义是什么? 替换一下就可以了.


------解决方案--------------------
探讨


那楼主都"以最新的一个日期"的确切定义是什么? 替换一下就可以了.


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

SQL code

create table AAAA
(ApplyDate date, name varchar(8), CardID varchar(25))

insert into AAAA
select '2007-03-08', '张三', '310223197011082030' union all
select '2007-03-07', '张三', '310223197011082030' union all
select '2007-03-06', '张三', '310223197011082030' union all
select '2006-03-05', '张三', '310223197011082030' union all
select '2006-03-04', '张三', '310223197011082030' union all
select '2007-03-05', '李四', '310223197011082031'


with t as
(