SQL分组查询每组前几条数据

利用Row_NUMBER()获取分组的前几条数据，获取前几条数据

/*第一种实现方法，效率低并且有错误*/
  DECLARE @DD DATETIME 
  SET @DD = GETDATE()

  SELECT    a.GoodsID ,
            a.Account ,
            a.LastUpdate
  FROM      dbo.tb_App_Goods a
            LEFT JOIN dbo.tb_App_Goods b ON a.Account = b.Account
                                            AND a.LastUpdate > b.LastUpdate
  GROUP BY  a.GoodsID ,
            a.Account ,
            a.LastUpdate
  HAVING    COUNT(b.GoodsID) < 2
  ORDER BY  a.Account ,
            a.LastUpdate DESC;
        
  PRINT DATEDIFF(MILLISECOND, @DD, GETDATE())
  
  /*第二种实现方法，效率高并且准确*/   
  SET @DD = GETDATE()
  SELECT    a.GoodsID ,
            a.Account ,
            a.LastUpdate
  FROM      dbo.tb_App_Goods AS a
            LEFT JOIN ( SELECT  ROW_NUMBER() OVER ( PARTITION BY Account ORDER BY LastUpdate DESC ) AS C ,
                                *
                        FROM    dbo.tb_App_Goods
                      ) AS b ON a.GoodsID = b.GoodsID
  WHERE     ( b.C BETWEEN 1 AND 2 )
  ORDER BY  a.Account ,
            a.LastUpdate DESC;
        
  PRINT DATEDIFF(MILLISECOND, @DD, GETDATE())      

执行结果：

明显第二种方法效率最高：

不仅仅是效率高，仔细看，查询结果不一样，第一种方法结果比第二种方法查询出来的多：

原因：

慎于行,敏于思！GGGGGG