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

时间:2022-06-19 00:49:19
  /*第一种实现方法,效率低并且有错误*/
  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())

执行结果:

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

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

 

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

原因:

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

第一种方法什么鬼,查询出来的还不够准确,,

总结:一定要用第二种方法

特此记录,,