在不连续的时间段YYYYMM00中找到MIN和MAX日期

夜晚涛声依旧

我有以下两个表：
DimensionTime    是一种表格，包括按压YYYMM00格式按ID每个月的排名
; y9 k" J, \0 @0 A. @% ^9 J/ v) FLogPlayer    是一种与玩家和特定月份相关的统计信息的表格。我想要的是以下内容：
2 L3 w" n! m( o( `/ a    -------- -------- ---------- ---------- | Player |  Team  |  Start   |   End    | -------- -------- ---------- ---------- | John   | Red    | 20180100 | 20180300 || John   | Red    | 20180600 | 20180700 || Luke   | Yellow | 20180100 | 20180100 || Luke   | Yellow | 20190100 | 20190100 | -------- -------- ---------- ---------- 我不能使用MIN和MAX函数，因为周期是不连续的…我该怎么解决呢？我尝试的MIN / MAX与GROUP
BY结合使用，但没用。我在那里Stackoverflow找不到任何问题或答案。
SELECT *    INTO #DimensionTime    FROM (       SELECT 1 AS [ID],20180100 AS [TIMEID]        UNION ALL        SELECT 2 AS [ID],20180200 AS [TIMEID]        UNION ALL        SELECT 3 AS [ID],20180300 AS [TIMEID]        UNION ALL        SELECT 4 AS [ID],20180400 AS [TIMEID]        UNION ALL        SELECT 5 AS [ID],20180500 AS [TIMEID]        UNION ALL        SELECT 6 AS [ID],20180600 AS [TIMEID]        UNION ALL        SELECT 7 AS [ID],20180700 AS [TIMEID]        UNION ALL        SELECT 8 AS [ID],20180800 AS [TIMEID]        UNION ALL        SELECT 9 AS [ID],20180900 AS [TIMEID]        UNION ALL        SELECT 10 AS [ID],20181000 AS [TIMEID]        UNION ALL        SELECT 11 AS [ID],20181100 AS [TIMEID]        UNION ALL        SELECT 12 AS [ID],20181200 AS [TIMEID]        UNION ALL        SELECT 13 AS [ID],20190100 AS [TIMEID]        UNION ALL        SELECT 14 AS [ID],20190200 AS [TIMEID]        UNION ALL        SELECT 15 AS [ID],20190300 AS [TIMEID]   ) ASELECT *INTO #LogPlayerFROM  SELECT 'John' AS [Player],'Red' AS [Team],20180100 AS [TIMEID]    UNION ALL    SELECT 'John' AS [Player],'Red' AS [Team],20180200 AS [TIMEID]    UNION ALL    SELECT 'John' AS [Player],'Red' AS [Team],20180300 AS [TIMEID]    UNION ALL    SELECT 'John' AS [Player],'Red' AS [Team],20180600 AS [TIMEID]    UNION ALL    SELECT 'John' AS [Player],'Red' AS [Team],20180700 AS [TIMEID]    UNION ALL    SELECT 'Luke' AS [Player],'Yellow' AS [Team],20180100 AS [TIMEID]    UNION ALL    SELECT 'Luke' AS [Player],'Yellow' AS [Team],20190100 AS [TIMEID]) B               
/ k$ ]& L9 r$ F9 N( C    解决方案:                                                               
. A0 b- j% ^. _8 ~1 _9 i6 \7 i                                                                这是一个空白与孤岛的问题。即使在不受支持的古代软件(如SQL Server 2005)也可以解决，因为这个版本有row_number()。
  b" S- E8 Q3 ?! U一个技巧是把时间花在上面id转换为 真实的    日期/时间。另一种技能是通过从日期/时间值中减去连续月数来定义组：
8 T* u& {; s, D% J* z, |select player,team,min(timeid),max(timeid)from (select lp.*,            row_number() over (partition by player,team order by timeid) as seqnum,            cast(cast(timeid   1 as varchar(255)) as datetime) as yyyymm      from logplayer lp     ) lpgroup by player,team,dateadd(month,- seqnum,yyyymm)order by player,team,min(timeid);这是一个db fiddle。