在保留id的同时避免每个轨道中的空对象(Avoiding empty objects in rails each while preserving ids)

 我有点像冒泡的方法。 我在进行递归操作时发现的问题是它们适用于小型集合（想想小于5或10k），然后当你变大时表现得很糟糕。 出于这个原因，我喜欢光标方法，你实际上是在说：“你是否大于标准？是，否。插入或忽略，删除，继续前进。” 这样，您只需对一次和每次一次的项目进行评估，而不是对递归主题的每个变体进行评估。 
 
DECLARE @Temp TABLE 
  (
    id INT 
  , dt DATETIME
  , txt VARCHAR(8)
  , rwn INT
  )

DECLARE @Holder TABLE 
  (
    id INT 
  , dt DATETIME
  , txt VARCHAR(8)
  , Dif int
  )

INSERT INTO @Temp
SELECT *, row_number() over (partition by txt order by dt, id) AS rn
From wt

WHILE EXISTS (SELECT 1 FROM @Temp)
BEGIN
    DECLARE 
      @CurId    INT
    , @CurDt    DATETIME
    , @Curtxt   VARCHAR(8)
    , @LastDate DATETIME
    ;

    SELECT TOP 1 @CurId = Id, @CurDt = Dt, @Curtxt = txt FROM @Temp ORDER BY txt, rwn

    --If there is not entry you need a single entry
    IF NOT EXISTS (SELECT TOP 1 * FROM @Holder)
       BEGIN
          INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, null)
       END
    ELSE
      --if you reset the grouping you need to reset and begin anew
      IF (SELECT rwn FROM @Temp WHERE Id = @CurId) = 1
        BEGIN
            INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, null)
        END
      --if you are going along check the logic for the difference of what the last was compared to the current
      ELSE
        BEGIN
         SELECT TOP 1 @LastDate = dt FROM @Holder ORDER BY id desc

         IF DATEDIFF(HOUR, @LastDate, @CurDt) >= 1
         BEGIN
             INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, DATEDIFF(MINUTE, @LastDate, @CurDt))
         END
        END

    --Delete the running values and loop again
    DELETE @Temp WHERE Id = @CurId
END

Select *
From @Holder

I kind of like a method that is a bubble sort. The problem I have found when doing recursive operations is they work great for small sets(think less than 5 or 10k), then behave horrid when you get larger. For this reason I like a cursor approach were you are essentially saying: "Are you larger than a criteria? Yes, No. Insert or Ignore, Delete, move on." This way you are evaluating over every item once and once only, not every variation of a theme of recursion.
 
DECLARE @Temp TABLE 
  (
    id INT 
  , dt DATETIME
  , txt VARCHAR(8)
  , rwn INT
  )

DECLARE @Holder TABLE 
  (
    id INT 
  , dt DATETIME
  , txt VARCHAR(8)
  , Dif int
  )

INSERT INTO @Temp
SELECT *, row_number() over (partition by txt order by dt, id) AS rn
From wt

WHILE EXISTS (SELECT 1 FROM @Temp)
BEGIN
    DECLARE 
      @CurId    INT
    , @CurDt    DATETIME
    , @Curtxt   VARCHAR(8)
    , @LastDate DATETIME
    ;

    SELECT TOP 1 @CurId = Id, @CurDt = Dt, @Curtxt = txt FROM @Temp ORDER BY txt, rwn

    --If there is not entry you need a single entry
    IF NOT EXISTS (SELECT TOP 1 * FROM @Holder)
       BEGIN
          INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, null)
       END
    ELSE
      --if you reset the grouping you need to reset and begin anew
      IF (SELECT rwn FROM @Temp WHERE Id = @CurId) = 1
        BEGIN
            INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, null)
        END
      --if you are going along check the logic for the difference of what the last was compared to the current
      ELSE
        BEGIN
         SELECT TOP 1 @LastDate = dt FROM @Holder ORDER BY id desc

         IF DATEDIFF(HOUR, @LastDate, @CurDt) >= 1
         BEGIN
             INSERT INTO @Holder VALUES (@CurId, @CurDt, @curtxt, DATEDIFF(MINUTE, @LastDate, @CurDt))
         END
        END

    --Delete the running values and loop again
    DELETE @Temp WHERE Id = @CurId
END

Select *
From @Holder