急...如何查询[用户表]中指定n个用户在[用户登录记录表]中的最新登录信息?
用户表:Tbl_User_Index
[UserId] [UserName] [UserGroup]
1 a Guest
2 b Viewer
3 c Guest
4 d Guest
. .
. .
. .
用户登录记录表:Tbl_Login_Info
[UserId] [LoginTime] ...
1 2007-01-01 08:00:00
1 2007-02-13 14:10:30
1 2007-05-11 12:14:32
2 2006-11-21 12:34:12
2 2007-04-11 13:22:45
3 2007-03-14 09:01:50
3 2007-02-13 14:10:30
4 2007-02-05 23:22:45
. .
. .
. .
例如:如何查找表Tbl_User_Index中[UserGroup]为Guest的用户在表Tbl_Login_Info中的最新记录?
结果格式:
UserName UserID LoginTime ...
a 1 2007-05-11 12:14:32
c 3 2007-03-14 09:01:50
d 4 2007-02-05 23:22:45
------解决方案--------------------select a.[UserId],
a.[UserName],
b.[LoginTime]
from Tbl_User_Index a
left join (select [UserId],max([LoginTime]) as [LoginTime] from Tbl_Login_Info group by [UserId])b on a.[UserId]=b.[UserId]
------解决方案--------------------create table Tbl_User_Index([UserId] int,[UserName] varchar(20),[UserGroup] varchar(20))
insert into Tbl_User_Index
select 1, 'a ', 'Guest '
union all select 2, 'b ', 'Viewer '
union all select 3, &