3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
选取最大记录，并且最大记录的日期和当前日期差几天！
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 的时候为空。
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 的时候为空。
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
weasea 2008-01-07
树上的鸟儿
如果大于6 答案对的
但是如果大于10
我想要的结果为空
谢谢
如果大于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 descid 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])
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())>6marco08 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)
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
*/