http://bbs.csdn.net/topics/390350488
原帖在这里,解决了那边登记一下也给分,那边我弄错了id name masterId createdate
1 aaa 55 2012-1-1
2 aaa 55 2012-5-1
3 bbb 61 2012-7-1不好意思,我弄错了,现在不是要找最大的创建时间,现在是要找:
1,如果只有一条记录,就和当前时间比,是否超了3个月
2.如果有多条记录,是否最近的两条间隔超了3个月由此,上面记录应该是id的2,3被选出来,或者直接返回55,56的masterid
原帖在这里,解决了那边登记一下也给分,那边我弄错了id name masterId createdate
1 aaa 55 2012-1-1
2 aaa 55 2012-5-1
3 bbb 61 2012-7-1不好意思,我弄错了,现在不是要找最大的创建时间,现在是要找:
1,如果只有一条记录,就和当前时间比,是否超了3个月
2.如果有多条记录,是否最近的两条间隔超了3个月由此,上面记录应该是id的2,3被选出来,或者直接返回55,56的masterid
FROM huang a INNER JOIN (
SELECT NAME
FROM huang
GROUP BY NAME
HAVING COUNT(name)=1)b ON a.NAME=b.NAME
WHERE DATEDIFF(mm,CONVERT(DATE,createdate),CONVERT(DATE,GETDATE()))>=3
1 aaa 55 2012-1-1
2 aaa 55 2012-5-1
3 bbb 61 2012-7-1不好意思,我弄错了,现在不是要找最大的创建时间,现在是要找:
1,如果只有一条记录,就和当前时间比,是否超了3个月
2.如果有多条记录,是否最近的两条间隔超了3个月关于第二点,aaa有2条,也可能有多条,现在要找出aaa最近的两条,比较createdate,比如现在的aaa,createdate间隔超过3个月了,如果没超过3个月,就把id=2,或者masterid=55选出来即可,我说明白没?
use Tempdb
go
declare @T table(id int,name varchar(3),masterid varchar(2),createdate varchar(10))insert @T select 1 ,'aaa',55 ,'2012-1-1'
union all select 2 ,'aaa' ,55,'2012-5-1'
union all select 3 ,'bbb' ,61, '2012-7-1'
union all select 4 ,'aaa' ,55, '2011-7-1'select id,name,masterid
from ( select *,
(select max(createdate) from @T as b where b.name=a.name and b.createdate<a.createdate) as priordate
from @T as a
where not exists(select 1 from @T as c where c.name=a.name and c.createdate>a.createdate ) ) as d
where abs(datediff(m,createdate,isnull(priordate,getdate())))>3/*
2 aaa 55
3 bbb 61
*/
insert #t
select 1,'aaa',55,'2012-1-1' union all
select 2,'aaa',55,'2012-5-1' union all
select 3,'bbb',61,'2012-7-1';with cte as
(
select *,ROW_NUMBER() OVER(partition by masterId order by createdate desc) as rn from #t
)select * from
(select * from cte where rn=1) a left join
(select * from cte where rn=2) b on a.masterId=b.masterId
where abs(DATEDIFF(mm,isnull(b.createdate,convert(date,GETDATE())),a.createdate))>=3/*
id name masterId createdate rn
----------- ---- ----------- ---------- --------------------
2 aaa 55 2012-05-01 1
3 bbb 61 2012-07-01 1*/