表 a
id(自动) userid start end
1 1001 2002-1-1 2003-1-1
2 1001 2003-1-1 2005-1-1
3 1001 2005-1-1 2007-1-1
4 1002 2002-1-1 2003-1-1
5 1003 2003-1-1 2005-1-1
6 1003 2005-1-1 2007-1-1
...
想 select datediff(day,end,getdate()) where id in
(select max(id) group by userid)
如果datediff<30 则把这一行插入另一个表
能得到结果:表b
userid start end
1001 2005-1-1 2007-1-1
1003 2005-1-1 2007-1-1
id(自动) userid start end
1 1001 2002-1-1 2003-1-1
2 1001 2003-1-1 2005-1-1
3 1001 2005-1-1 2007-1-1
4 1002 2002-1-1 2003-1-1
5 1003 2003-1-1 2005-1-1
6 1003 2005-1-1 2007-1-1
...
想 select datediff(day,end,getdate()) where id in
(select max(id) group by userid)
如果datediff<30 则把这一行插入另一个表
能得到结果:表b
userid start end
1001 2005-1-1 2007-1-1
1003 2005-1-1 2007-1-1
as
begin
select id,datediff(day,end,getdate())as dif into #
from a
where id in(select max(id) group by userid) insert b
select userid start end from a
where id in(select id from # where dif<30)
go
insert @a select 1001 ,'2002-1-1', '2003-1-1'
union all select 1001 ,'2003-1-1', '2005-1-1'
union all select 1001 ,'2005-1-1', '2007-1-1'
union all select 1002 ,'2002-1-1', '2003-1-1'
union all select 1003 ,'2003-1-1', '2005-1-1'
union all select 1003, '2005-1-1', '2007-1-1'select a.id,a.userid,start,[end]
from(
select id,datediff(day,[end],getdate()) ss from @a a where not exists(select 1 from @a where userid=a.userid and id>a.id) ) tmp
Inner Join @a a
On a.id=tmp.id where ss<30
select userid,start,[end]
from 表a a
where [id] = (select max([id]) from 表
where userid=a.userid)
and datediff(d,[end],getdate())<30
Go
go
create table a( id int IDENTITY(1,1), userid int, start datetime, [end] datetime)
insert a
select 1001, '2002-1-1', '2003-1-1' union all
select 1001, '2003-1-1', '2005-1-1' union all
select 1001, '2005-1-1', '2007-1-1' union all
select 1002, '2002-1-1', '2003-1-1' union all
select 1003, '2003-1-1', '2005-1-1' union all
select 1003, '2005-1-1', '2007-1-1'
goif object_id('b') is not null drop table b
go
create table b( userid int, start datetime, [end] datetime)
gocreate proc test_proc
as
begin
select id,datediff(day,[end],getdate())as dif into #
from a
where id in(select max(id) from a group by userid) insert b
select userid,start,[end] from a
where id in(select id from # where dif<30)
end
goexec test_proc
select * from b--结果
/*
userid start end
1001 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000
1003 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000
*/