SQL----查询所有用户最后一天的数据

CREATE TABLE UserLog
(
    UserId INT NOT NULL,
    UpdateDate DATETIME NOT NULL DEFAULT GETDATE(),
    [Action] VARCHAR(300) NOT NULL
)
GO
INSERT INTO UserLog(UserId,[Action])VALUES(1,'开始')
INSERT INTO UserLog(UserId,[Action])VALUES(1,'麦仁')
INSERT INTO UserLog(UserId,[Action])VALUES(1,'理事')
INSERT INTO UserLog(UserId,[Action])VALUES(1,'结束')
INSERT INTO UserLog(UserId,[Action])VALUES(2,'开始')
INSERT INTO UserLog(UserId,[Action])VALUES(2,'麦仁')
INSERT INTO UserLog(UserId,[Action])VALUES(3,'理事')
INSERT INTO UserLog(UserId,[Action])VALUES(4,'结束')

INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-99,GETDATE()),'开始')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-98,GETDATE()),'麦仁')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-97,GETDATE()),'理事')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(1,DATEADD(hh,-96,GETDATE()),'结束')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(2,DATEADD(hh,-95,GETDATE()),'开始')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(2,DATEADD(hh,-94,GETDATE()),'麦仁')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(3,DATEADD(hh,-93,GETDATE()),'理事')
INSERT INTO UserLog(UserId,UpdateDate,[Action])VALUES(4,DATEADD(hh,-92,GETDATE()),'结束')
GO
SELECT * FROM UserLog ul
GO
--显示数据如下：

--第一步 根据用户分组 取日期最大值
WITH L AS 
(
    SELECT ul.UserId,convert(varchar(10),MAX(ul.UpdateDate),120) UpdateDate FROM UserLog ul GROUP BY ul.UserId
)
--第二步 左外连接 主表 匹配 用户和日期
SELECT ul.* FROM L l LEFT JOIN UserLog ul 
ON l.UserId = ul.UserId AND CONVERT(VARCHAR(10),ul.UpdateDate,120) = l.UpdateDate
GO
--显示数据如下：

OK 想要的数据就这样查询出来了。