22,209
社区成员
发帖
与我相关
我的任务
分享
create table #t([id] int,[name] varchar(3),[masterId] int,[createdate] date)
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
*/
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
*/
SELECT a.*
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