SQL大牛求一复杂SQL语句,该如何解决

create table AAAA(ApplyDate date, name varchar(8), CardID varchar(25))insert into AAAAselect '2007-03-08', '张三', '310223197011082030' union allselect '2007-03-07', '张三', '310223197011082030' union allselect '2007-03-06', '张三', '310223197011082030' union allselect '2006-03-05', '张三', '310223197011082030' union allselect '2006-03-04', '张三', '310223197011082030' union allselect '2007-03-05', '李四', '310223197011082031'with t as(select * from AAAAwhere 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 张三       3102231970110820302007-03-07 张三       3102231970110820302007-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 AAAAselect '2007-03-08', '张三', '310223197011082030' union allselect '2007-03-07', '张三', '310223197011082030' union allselect '2007-03-06', '张三', '310223197011082030' union allselect '2006-03-05', '张三', '310223197011082030' union allselect '2006-03-04', '张三', '310223197011082030' union allselect '2007-03-05', '李四', '310223197011082031'with t as(select a.* from AAAA ainner join(select name, max(applydate) maxd, dateadd(d,-180,max(ApplyDate)) mind  from AAAA group by name) bon a.name=b.name and a.applydate between b.mind and b.maxd)select * from t where name in (select name from t group by name having count(*)>=3)ApplyDate   name     CardID---------- -------- -------------------------2007-03-08  张三       3102231970110820302007-03-07  张三       3102231970110820302007-03-06  张三       310223197011082030