分区内每个序列的最大日期 [英] Max dates for each sequence within partitions

问题描述

我想看看是否有人知道如何使用row_num"列作为序列在 SQL Server 2016 中开始/结束时的指示符来获取每个id"中的最大和最小日期.

I would like to see if somebody has an idea how to get the max and min dates within each 'id' using the 'row_num' column as an indicator when the sequence starts/ends in SQL Server 2016.

下面的屏幕截图显示了min_date"和max_date"列中所需的输出.

The screenshot below shows the desired output in columns 'min_date' and 'max_date'.

任何帮助将不胜感激.

推荐答案

尝试类似

SELECT 
    Q1.id, Q1.cat, 
    MIN(Q1.date) AS min_dat, 
    MAX(Q1.date) AS max_dat 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (PARTITION BY id, cat ORDER BY [date]) AS r1, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY [date]) AS r2
    ) AS Q1
GROUP BY 
    Q1.id, Q1.r2 - Q1.r1

这篇关于分区内每个序列的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！