SQL Where子句变量需要每行不同

Question

给定以下数据：

 ID, Name, Location, Date
 1,  Tom,  Boston, 8/12/2015
 1,  Tom,  Lowell, 7/12/2015
 1,  Tom,  Wakefield, 6/12/2015
 2,  Jake, Salem, 12/15/2018
 2,  Jack, Worcester, 5/12/2015

如何编写一个SQL查询来返回一个只包含最大日期的行的表，例如：

ID, Name, Location, Date
 1,  Tom,  Boston, 8/12/2015
 2,  Jake, Salem, 12/15/2018

假设ID和Name是固定密钥。但是，location可以是可变的，应该返回的location将是与最大date相关联的one。

我最近的尝试当前仅返回所有日期中的最大日期，而不返回具有ID / Name对的最大日期的记录。例如：

SELECT ID, NAME, Location, Data
 FROM Table A
 WHERE Date = (SELECT MAX(Date) FROM Table B WHERE A.ID = B.ID and A.Name = B.Name)

仅收益：

2,  Jake, Salem, 12/15/2018

Answer 1

方法1：

SELECT A.* FROM [Table] A
JOIN (
    SELECT ID, Name, MAX([Date]) MaxDate
    FROM [Table]
    GROUP BY ID, Name
) B ON B.ID = A.ID AND B.Name = A.Name AND B.MaxDate = A.[Date]

方法二：

SELECT ID, Name, [Location], [Date] FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY [Date] DESC) R
    FROM [Table]
) T
WHERE R = 1

示例：

DECLARE @T TABLE (
    ID INT,
    Name VARCHAR(16),
    [Location] VARCHAR(16),
    [Date] DATE
)

INSERT @T VALUES
(1, 'Tom', 'Boston', '8/12/2015'),
(1, 'Tom', 'Lowell', '7/12/2015'),
(1, 'Tom', 'Wakefield', '6/12/2015'),
(2, 'Jake', 'Salem', '12/15/2018'),
(2, 'Jack', 'Worcester', '5/12/2015')

SELECT A.* FROM @T A
JOIN (
    SELECT ID, Name, MAX([Date]) MaxDate
    FROM @T
    GROUP BY ID, Name
) B ON B.ID = A.ID AND B.Name = A.Name AND B.MaxDate = A.[Date]

SELECT ID, Name, [Location], [Date] FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY [Date] DESC) R
    FROM @T
) T
WHERE R = 1

Answer 2

类似这样的东西是有效的：

select ID, Name, Location, Date
from (
  select *, row_number() over (partition by ID order by Date desc) as RN
  from yourtable
) X
where RN = 1

行号将按日期降序为行添加一个序列号，分区方式意味着每次ID更改时都将重新开始编号。

如果您需要获取多个行，以防有多个行在同一天，则使用rank()而不是row_number()

Answer 3

您可以通过按Id字段分组来查找最大日期。然后用主表join查找最终结果。

SELECT X.*
FROM @T X INNER JOIN 
                    (
                        SELECT A.ID,MAX(A.Date) MaxDate
                        FROM @T A
                        GROUP BY A.ID
                                        ) Y 
ON X.ID = Y.Id AND x.Date = Y.MaxDate
ORDER BY Y.ID