现有两个表 表1 user (用户信息 ) username password
A 123
B 000
C 555
表2 record (用户记录)
id username logintime
1 C 2009-10-10
2 A 2009-10-11
3 A 2009-10-12
4 B 2009-10-13
5 B 2009-10-15
6 C 2009-10-16
表2 id列是自动编号,recordtime是最后登陆时间时间,现在要显示表1的所有信息 及最后记录时间,结果应该如下username password logintime
A 123 2009-10-12
B 000 2009-10-15
C 555 2009-10-16请问这条SQL语句如何写
A 123
B 000
C 555
表2 record (用户记录)
id username logintime
1 C 2009-10-10
2 A 2009-10-11
3 A 2009-10-12
4 B 2009-10-13
5 B 2009-10-15
6 C 2009-10-16
表2 id列是自动编号,recordtime是最后登陆时间时间,现在要显示表1的所有信息 及最后记录时间,结果应该如下username password logintime
A 123 2009-10-12
B 000 2009-10-15
C 555 2009-10-16请问这条SQL语句如何写
left join
(select max(logintime)as lasttime,username from record group by username)
as b
on a.username = b.username
left join
(select username,max(logintime)as lasttime from record group by username) t2
on t1.username = t2.username
if exists(select 1 from sys.objects where object_id=object_id('[user]') and type='u')
drop table [user]
create table [user](username varchar(1),password varchar(3))
if exists(select 1 from sys.objects where object_id=object_id('record') and type='u')
drop table record
create table record(id int identity(1,1),username varchar(3),logintime varchar(10))delete from [user]
delete from recordinsert into [user](username,password)select 'A','123' union all
select 'B','000' union all
select 'C','555'
insert into record(username,logintime) select 'C','2009-10-10' union all
select 'A','2009-10-11' union all
select 'A' ,'2009-10-12' union all
select 'B','2009-10-13' union all
select 'B','2009-10-15' union all
select 'C','2009-10-16'select a.*,max(b.logintime)logintime from [user] a,record b group by a.username,a.password
username,password logintime
B 000 2009-10-16
A 123 2009-10-16
C 555 2009-10-16
left join
(select max(id) as id,username from record group by username)
as b
on a.username = b.username
left join
record as c
on b.id=c.id