sql - 简化 SQL 选择语句

Question

我有这样结构的表

CREATE TABLE UsersHistory
(
    Id          INT IDENTITY,
    UserID      INT,
    StatusId    INT,
    CreateTime  DATETIME,
    ChangedTime DATETIME
)

INSERT INTO UsersHistory(UserID, StatusId, CreateTime, ChangedTime)
SELECT 1,1,'20150414','20150414' UNION ALL
SELECT 1,2,'20150414','20150415' UNION ALL
SELECT 1,3,'20150414','20150416' UNION ALL
SELECT 2,1,'20150413','20150413' UNION ALL
SELECT 2,3,'20150413','20150416'

和查询

;WITH k AS (
SELECT uh.UserID,MAX(uh.ChangedTime) AS Dt FROM UsersHistory AS uh
WHERE uh.ChangedTime<'20150416'
GROUP BY uh.UserID
)
SELECT k.UserID,uh.StatusId FROM k
INNER JOIN UsersHistory AS uh
    ON k.UserID = uh.UserID AND k.Dt = uh.ChangedTime

查询太简单了，不需要更多的解释。我想简化它。 (删除与日期时间类型列的连接)。

有什么建议吗？

Answer 1

您可以使用 ROW_NUMBER()与 PARTITION为达到这个。像这样的东西

;WITH CTE as 
( 
    SELECT UserID, StatusId, CreateTime, ChangedTime,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY ChangedTime DESC) r
    FROM UsersHistory
    WHERE ChangedTime < '20150416'
)
SELECT UserID, StatusId FROM CTE
WHERE r = 1