如何使用T-SQL从组中获取基于日期的记录
如何使用T-SQL从组中获取基于日期的记录
提问于 2010-02-21 15:56:17
以下是数据
Flag Zone Info Date
R North AAA 2010-2-14
R North AAA 2010-2-24
T North AAA 2010-2-4
R South AAA 2010-2-23
T South AAA 2010-2-14
R EAST AAA 2010-2-22
T EAST AAA 2010-2-11
T EAST AAA 2010-2-1
T EAST AAA 2010-2-14
R WEST AAA 2010-2-29这是SQL服务器中的一个表，现在我想根据Zone列从每个组中获取一条记录。此记录的Flag字段应为R，且日期应为最接近且晚于今天的日期。
诚挚的问候,
回答 1
Stack Overflow用户
回答已采纳
发布于 2010-02-21 16:05:25
使用ROW_NUMBER，您可以尝试
DECLARE @Table TABLE(
Flag VARChAR(1),
Zone VARCHAR(10),
Info VARCHAR(10),
Date DATETIME
)
INSERT INTO @Table SELECT 'R','North','AAA','2010-2-14'
INSERT INTO @Table SELECT 'R','North','AAA','2010-2-24'
INSERT INTO @Table SELECT 'T','North','AAA','2010-2-4'
INSERT INTO @Table SELECT 'R','South','AAA','2010-2-23'
INSERT INTO @Table SELECT 'T','South','AAA','2010-2-14'
INSERT INTO @Table SELECT 'R','EAST',' AAA','2010-2-22'
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-11'
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-1'
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-14'
INSERT INTO @Table SELECT 'R','WEST',' AAA','2010-2-28'
;WITH Dates AS(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Zone ORDER BY Date) ROWID
FROM @Table
WHERE Flag = 'R'
AND Date > GETDATE()
)
SELECT *
FROM Dates
WHERE ROWID = 1如果您不能使用ROW_NUMBER，您可以尝试
SELECT t.*
FROM (
SELECT Zone,
MAX(Date) MaxDate
FROM @Table
WHERE Flag = 'R'
AND Date > GETDATE()
GROUP BY Zone
) Dates INNER JOIN
@Table t ON Dates.Zone = t.Zone and Dates.MaxDate = t.Date但这并不排除重复的.
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2305253
复制相关文章
相似问题
腾讯云开发者
