--表结构：
if exists (select * from sysobjects where id = OBJECT_ID('[表1]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [表1]
CREATE TABLE [表1] ( [ID] [int] NULL , [NAME] [varchar] (10) NULL , [QQ] [varchar] (10) NULL , [PHONE] [varchar] (20) NULL )
INSERT [表1] ( [ID] , [NAME] , [QQ] , [PHONE] ) VALUES ( 1 , '秦云' , '10102800' , '13500000' )
INSERT [表1] ( [ID] , [NAME] , [QQ] , [PHONE] ) VALUES ( 2 , '在路上' , '10378' , '13600000' )
INSERT [表1] ( [ID] , [NAME] , [QQ] , [PHONE] ) VALUES ( 3 , 'LEO' , '10000' , '13900000' )
if exists (select * from sysobjects where id = OBJECT_ID('[表2]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [表2]
CREATE TABLE [表2] ( [ID] [int] NULL , [NAME] [varchar] (10) NULL , [上机时间] [datetime] NULL , [管理员] [varchar] (10) NULL )
INSERT [表2] ( [ID] , [NAME] , [上机时间] , [管理员] ) VALUES ( 1 , '秦云' , '2004-01-01 00:00:00.000' , '李大伟' )
INSERT [表2] ( [ID] , [NAME] , [上机时间] , [管理员] ) VALUES ( 2 , '秦云' , '2005-01-01 00:00:00.000' , '马化腾' )
INSERT [表2] ( [ID] , [NAME] , [上机时间] , [管理员] ) VALUES ( 3 , '在路上' , '2005-07-01 00:00:00.000' , '马化腾' )
INSERT [表2] ( [ID] , [NAME] , [上机时间] , [管理员] ) VALUES ( 4 , '秦云' , '2005-09-01 00:00:00.000' , '李大伟' )
INSERT [表2] ( [ID] , [NAME] , [上机时间] , [管理员] ) VALUES ( 5 , '在路上' , '2005-02-01 00:00:00.000' , '李大伟' )
-----------------------------------------------------------------------------
--查找最近一次登陆记录（不包含从未登陆过的人的记录）
select A.Name,CA.上机时间,CA.管理员 from 表1 AS A
cross apply
(select top 1 Name,上机时间,管理员 from 表2 AS B
where A.Name = B.Name
order by 上机时间 desc) AS CA
SELECT A.Name ,CA.上机时间,CA.管理员 From(
select distinct Name from 表2) AS A
cross apply
(select top 1 Name,上机时间,管理员 from 表2 AS B
where A.Name = B.Name
order by 上机时间 desc) AS CA
----------------------------------------------------------------------------
--查找最近一次登陆记录（包含从未登陆过的人的记录，只不过记录用null表示）
select A.Name,CA.上机时间,CA.管理员 from 表1 AS A
outer apply
(select top 1 Name,上机时间,管理员 from 表2 AS B
where A.Name = B.Name
order by 上机时间 desc) AS CA