表结构如下:
ID CurDate aa
111 2005-6-1 2
111 2005-7-1 3
111 2005-8-1 4
111 2005-10-1 4
111 2005-12-1 5
222 2004-10-1 2
222 2004-12-1 1
222 2005-1-1 2
222 2005-2-1 1
....
我现在需要从上述数据中查出同一ID没有前一个月的记录的数据,但是要排除同一ID的最小月份的记录,要求得到结果是:111 2005-10-1 4
111 2005-12-1 5
222 2004-12-1 1请问各位高手怎么写啊??我自己写的一个函数效率太低,希望有更高效率的,因为我的表比较大,查询很慢
ID CurDate aa
111 2005-6-1 2
111 2005-7-1 3
111 2005-8-1 4
111 2005-10-1 4
111 2005-12-1 5
222 2004-10-1 2
222 2004-12-1 1
222 2005-1-1 2
222 2005-2-1 1
....
我现在需要从上述数据中查出同一ID没有前一个月的记录的数据,但是要排除同一ID的最小月份的记录,要求得到结果是:111 2005-10-1 4
111 2005-12-1 5
222 2004-12-1 1请问各位高手怎么写啊??我自己写的一个函数效率太低,希望有更高效率的,因为我的表比较大,查询很慢
from ta a
left join ta b on datediff(mi,a.CurDate,b.CurDate) = 1
insert @t select
111 ,'2005-6-1', 2 union select
111 ,'2005-7-1', 3 union select
111 ,'2005-8-1', 4 union select
111 ,'2005-10-1', 4 union select
111 ,'2005-12-1', 5 union select
222 ,'2004-10-1', 2 union select
222 ,'2004-12-1', 1 union select
222 ,'2005-1-1', 2 union select
222 ,'2005-2-1', 1
select distinct a.*
from @t a
where not exists(select 1 from @t where id = a.id and datediff(mm,curdate,a.curdate) = 1) and
curdate not in (select min(curdate) from @t group by id)/*
ID CurDate aa
----------- ------------------------------------------------------ -----------
111 2005-10-01 00:00:00.000 4
111 2005-12-01 00:00:00.000 5
222 2004-12-01 00:00:00.000 1(所影响的行数为 3 行)*/
insert @t select
111 ,'2005-6-1', 2 union select
111 ,'2005-7-1', 3 union select
111 ,'2005-8-1', 4 union select
111 ,'2005-10-1', 4 union select
111 ,'2005-12-1', 5 union select
222 ,'2004-10-1', 2 union select
222 ,'2004-12-1', 1 union select
222 ,'2005-1-1', 2 union select
222 ,'2005-2-1', 1
select distinct a.*
from @t a
where not exists(select 1 from @t where id = a.id and datediff(mm,curdate,a.curdate) = 1)
and
exists(select 1 from @t where id = a.id and curdate < a.curdate) /*
ID CurDate aa
----------- ------------------------------------------------------ -----------
111 2005-10-01 00:00:00.000 4
111 2005-12-01 00:00:00.000 5
222 2004-12-01 00:00:00.000 1(所影响的行数为 3 行)*/
insert into tb values(111 , '2005-6-1' , 2)
insert into tb values(111 , '2005-7-1' , 3)
insert into tb values(111 , '2005-8-1' , 4)
insert into tb values(111 , '2005-10-1', 4)
insert into tb values(111 , '2005-12-1', 5)
insert into tb values(222 , '2004-10-1', 2)
insert into tb values(222 , '2004-12-1', 1)
insert into tb values(222 , '2005-1-1' , 2)
insert into tb values(222 , '2005-2-1' , 1)
insert into tb values(223 , '2005-1-1' , 1)
goselect t.* from tb t where not exists(select 1 from tb where id = t.id and convert(varchar(7),curdate,120) = convert(varchar(7),dateadd(mm,-1,t.curdate),120))
and not exists (select 1 from (select id , min(curdate) curdate from tb group by id) n where id = t.id and convert(varchar(7),curdate,120) = convert(varchar(7),t.curdate,120))drop table tb/*
ID CurDate aa
----------- ------------------------------------------------------ -----------
111 2005-10-01 00:00:00.000 4
111 2005-12-01 00:00:00.000 5
222 2004-12-01 00:00:00.000 1(所影响的行数为 3 行)
*/