导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

weasea 2008-01-07 04:11:08
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 的时候为空。
...全文
67 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
weasea 2008-01-07
树上的鸟儿

如果大于6 答案对的
但是如果大于10
我想要的结果为空
谢谢
回复
wzy_love_sly 2008-01-07
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
回复
-狙击手- 2008-01-07
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

*/
回复
marco08 2008-01-07
--错了,改改

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])
回复
chuifengde 2008-01-07
select max(date) from [tbl] where usename='user1' and datediff(day,date,getdate())>6
回复
marco08 2008-01-07
--try

select *,相差=datediff(day,ddate,getdate()) from tb1 as tmp
where not exists(select * from tb1 where username=tmp.username and ddate>tmp.username)
回复
-狙击手- 2008-01-07
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
*/
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告