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

SQL大牛求一复杂SQL语句
这样一个表
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

[解决办法]

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 * 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)


/*
ApplyDatenameCardID
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 
 
[解决办法]
SQL code
DECLARE @table table (ApplyDate date, name varchar(8), CardID varchar(25))insert into @tableselect '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'SELECT  MAX(ApplyDate),name,a.CardID  FROM @table  a LEFT JOIN (    SELECT MAX(ApplyDate) AS BDate,CardID,DATEADD(DAY,-180,MAX(ApplyDate))             AS LastApplyDate FROM @table GROUP BY CardID    ) b ON a.CardID = b.CardIDWHERE a.ApplyDate BETWEEN LastApplyDate AND BDateGROUP BY a.CardID,name HAVING COUNT(1)>2
[解决办法]
SQL code
create table AAAA(ApplyDate datetime, 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'select t.* from(select m.name from aaaa m ,(select max(ApplyDate) ApplyDate , name from aaaa group by name) nwhere m.name = n.name and datediff(dd,m.ApplyDate , n.ApplyDate) <= 180group by m.name having count(1) >= 3) t1 , aaaa t,(select max(ApplyDate) ApplyDate , name from aaaa group by name) t2where t.name = t1.name and t1.name = t2.name and datediff(dd,t.ApplyDate , t2.ApplyDate) <= 180/*ApplyDate                                              name     CardID                    ------------------------------------------------------ -------- ------------------------- 2007-03-08 00:00:00.000                                张三       3102231970110820302007-03-07 00:00:00.000                                张三       3102231970110820302007-03-06 00:00:00.000                                张三       310223197011082030（所影响的行数为 3 行）*/drop table aaaa