选取最大记录，并且最大记录的日期和当前日期差几天！

[weasea]

tbl

id username date
1 user1 2007-12-20
2 user2 2007-12-31
3 user1 2008-1-1

选取username 是user1，date为最大 并且和当前日期相差N天。
比如 今天是2008-1-7 。和
记录
3 user1 2008-1-1

相差 6天

当N〉6的时候。都是选第三条记录
当N<6 的时候为空。

[-狙击手-]

set nocount on
declare @t table(id int,username varchar(10),date  datetime)
insert into @t select 1,'user1','2007-12-20' 
insert into @t select 2,'user2','2007-12-31' 
insert into @t select 3,'user1','2008-1-1'


select username,max(date),datediff(d,max(date) ,getdate()) as 差
from @t
where username = 'user1'
group by username

/*

username                                                          差           
---------- ------------------------------------------------------ ----------- 
user1      2008-01-01 00:00:00.000                                6
*/

[marco08]

--try

select *,相差=datediff(day,ddate,getdate()) from tb1 as tmp
where not exists(select * from tb1 where username=tmp.username and ddate>tmp.username)

[chuifengde]

select max(date) from [tbl] where usename='user1' and datediff(day,date,getdate())>6

[marco08]

--错了,改改

create table T
(
id int,
username nvarchar(10),
[date] datetime
)

insert T
select 1, 'user1', '2007-12-20'
union all select 2, 'user2', '2007-12-31'
union all select 3, 'user1', '2008-01-01'

select *,相差=datediff(day,[date],getdate()) from T as tmp
where not exists(select * from T where username=tmp.username and [date]>tmp.[date])

[-狙击手-]

set nocount on
declare @t table(id int,username varchar(10),date  datetime)
insert into @t select 1,'user1','2007-12-20' 
insert into @t select 2,'user2','2007-12-31' 
insert into @t select 3,'user1','2008-1-1'


select username,max(date),datediff(d,max(date) ,getdate()) as 差
from @t
where username = 'user1'
group by username
/*

username                                                          差           
---------- ------------------------------------------------------ ----------- 
user1      2008-01-01 00:00:00.000                                6
*/

select *,datediff(d,[date],getdate())
from @t a
where datediff(d,[date],getdate()) < 6
and not exists(select 1 from @t where a.username = username and [date]>a.[date])
and username = 'user1'
/*
id          username   date                                                               
----------- ---------- ------------------------------------------------------ ----------- 

*/

select *,datediff(d,[date],getdate())
from @t a
where datediff(d,[date],getdate()) <= 6
and not exists(select 1 from @t where a.username = username and [date]>a.[date])
and username = 'user1'
/*
id          username   date                                                               
----------- ---------- ------------------------------------------------------ ----------- 
3           user1      2008-01-01 00:00:00.000                                6

*/

[wzy_love_sly]

declare @tb table (id int,username varchar(10),date datetime)
insert into @tb select 1,'user1','2007-12-20'
insert into @tb select 2,'user2','2007-12-31'
insert into @tb select 3,'user1','2008-1-1'

declare @dt datetime
set @dt = '2008-1-7'
select top 1 * from @tb where dateadd(dd,-6, @dt)>=date order by date desc

id username date
3 user1 2008-01-01 00:00:00.000

[weasea]

树上的鸟儿

如果大于6 答案对的
但是如果大于10
我想要的结果为空
谢谢