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

  /*第一种实现方法，效率低并且有错误*/
  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())