--> 测试数据: #log
if object_id('tempdb.dbo.#log') is not null drop table #log
create table #log (useid int,addtime datetime,topic varchar(50))
insert into #log
select 1,'2008-01-11 12:59:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-03-21 10:52:00',null union all
select 1,'2008-04-03 11:40:00',null union all
select 2,'2008-04-02 10:27:00',null union all
select 3,'2008-04-02 10:32:00',null union all
select 4,'2008-04-02 10:34:00',null union all
select 5,'2008-04-02 10:35:00',null
--> 测试数据: #user
if object_id('tempdb.dbo.#user') is not null drop table #user
create table #user (userid int,username varchar(6),addtime datetime)
insert into #user
select 1,'gelan',null union all
select 2,'test',null union all
select 3,'myname',null union all
select 4,'john',null union all
select 5,'lucy',nullselect * from #log a where addtime=(select max(addtime) from #log where useid=a.useid)update a set a.addtime=(select max(addtime) from #log where useid=a.userid) from #user aselect * from #user
if object_id('tempdb.dbo.#log') is not null drop table #log
create table #log (useid int,addtime datetime,topic varchar(50))
insert into #log
select 1,'2008-01-11 12:59:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-01-11 13:00:00',null union all
select 1,'2008-03-21 10:52:00',null union all
select 1,'2008-04-03 11:40:00',null union all
select 2,'2008-04-02 10:27:00',null union all
select 3,'2008-04-02 10:32:00',null union all
select 4,'2008-04-02 10:34:00',null union all
select 5,'2008-04-02 10:35:00',null
--> 测试数据: #user
if object_id('tempdb.dbo.#user') is not null drop table #user
create table #user (userid int,username varchar(6),addtime datetime)
insert into #user
select 1,'gelan',null union all
select 2,'test',null union all
select 3,'myname',null union all
select 4,'john',null union all
select 5,'lucy',nullselect * from #log a where addtime=(select max(addtime) from #log where useid=a.useid)update a set a.addtime=(select max(addtime) from #log where useid=a.userid) from #user aselect * from #user
delete a from #log a where exists (select 1 from #log where useid=a.useid and addtime>a.addtime)
insert @log(userid,addtime) select 1,'2008-01-11 12:59:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-03-21 10:52:00'
union all select 1,'2008-04-03 11:40:00'
union all select 2,'2008-04-02 10:27:00'
union all select 3,'2008-04-02 10:32:00'
union all select 4,'2008-04-02 10:34:00'
union all select 5,'2008-04-02 10:35:00'declare @user table(userid int,username varchar(10),addtime datetime)
insert @user(userid,username)
select 1,'gelan'
union all select 2,'test'
union all select 3,'myname'
union all select 4,'john'
union all select 5,'lucy'
update u
set addtime=(select max(addtime) from @log where userid=u.userid)
from @user uselect * from @user/*
userid username addtime
----------- ---------- ------------------------------------------------------
1 gelan 2008-04-03 11:40:00.000
2 test 2008-04-02 10:27:00.000
3 myname 2008-04-02 10:32:00.000
4 john 2008-04-02 10:34:00.000
5 lucy 2008-04-02 10:35:00.000
*/
useid addtime
------------------------------
1 2008-01-11 12:59:00
1 2008-01-11 13:00:00
1 2008-01-11 13:00:00
1 2008-01-11 13:00:00
1 2008-03-21 10:52:00
1 2008-04-03 11:40:00
2 2008-04-02 10:27:00
3 2008-04-02 10:32:00
4 2008-04-02 10:34:00
5 2008-04-02 10:35:00
表user
userid username
-----------------------------
1 gelan
2 test
3 myname
4 john
5 lucy 要求是最后得到的记录集至少包含userid,username这两列,要按addtime倒序排列
---借数据declare @log table(userid int,addtime datetime,topic varchar(100))
insert @log(userid,addtime) select 1,'2008-01-11 12:59:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-03-21 10:52:00'
union all select 1,'2008-04-03 11:40:00'
union all select 2,'2008-04-02 10:27:00'
union all select 3,'2008-04-02 10:32:00'
union all select 4,'2008-04-02 10:34:00'
union all select 5,'2008-04-02 10:35:00'declare @user table(userid int,username varchar(10),addtime datetime)
insert @user(userid,username)
select 1,'gelan'
union all select 2,'test'
union all select 3,'myname'
union all select 4,'john'
union all select 5,'lucy'select a.Userid,a.username,b.addtime from @user a,
(
select * from @log a
where not exists(select 1 from @log b where a.Userid=b.userid and a.addtime<b.addtime)
) b
where a.userid=b.userid
Userid username addtime
----------- ---------- -----------------------
1 gelan 2008-04-03 11:40:00.000
2 test 2008-04-02 10:27:00.000
3 myname 2008-04-02 10:32:00.000
4 john 2008-04-02 10:34:00.000
5 lucy 2008-04-02 10:35:00.000(5 行受影响)
select *,addtime=(select max(addtime) from @log where a.userid=userid ) from @user a
order by a.addtime desc
userid username addtime addtime
----------- ---------- ----------------------- -----------------------
1 gelan NULL 2008-04-03 11:40:00.000
2 test NULL 2008-04-02 10:27:00.000
3 myname NULL 2008-04-02 10:32:00.000
4 john NULL 2008-04-02 10:34:00.000
5 lucy NULL 2008-04-02 10:35:00.000(5 行受影响)
insert @log(userid,addtime) select 1,'2008-01-11 12:59:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-01-11 13:00:00'
union all select 1,'2008-03-21 10:52:00'
union all select 1,'2008-04-03 11:40:00'
union all select 2,'2008-04-02 10:27:00'
union all select 3,'2008-04-02 10:32:00'
union all select 4,'2008-04-02 10:34:00'
union all select 5,'2008-04-02 10:35:00'declare @user table(userid int,username varchar(10),addtime datetime)
insert @user(userid,username) select 1,'gelan'
union all select 2,'test'
union all select 3,'myname'
union all select 4,'john'
union all select 5,'lucy'select a.userid,b.username,a.addtime
from
( select userid,addtime
from @log l
where addtime=(select max(addtime) from @log where userid=l.userid)
)a join @user b on a.userid=b.userid
order by a.userid/*
userid username addtime
----------- ---------- ------------------------------------------------------
1 gelan 2008-04-03 11:40:00.000
2 test 2008-04-02 10:27:00.000
3 myname 2008-04-02 10:32:00.000
4 john 2008-04-02 10:34:00.000
5 lucy 2008-04-02 10:35:00.000
*/
能详细解释一下这句吗?特别是后面的userid=l.userid
为什么要这么用呢