SQLServer分组加序号，只取某个对象指定条件的前几个

 --  -- 删除base里冗余的数据
  --UPDATE dbo.N_Order_ServiceLog SET IsDel = 1 WHERE OrderId IN (
  SELECT OrderId FROM(
          SELECT ROW_NUMBER() OVER(PARTITION BY bs.Phone,sl.Companyid ORDER BY  sl.id desc) AS cindex,
           bs.Phone,sl.* FROM dbo.TableXXXXX   bs
 LEFT JOIN TableLLLLL  sl
 ON sl.OrderId = bs.OrderId
 WHERE bs.OrderType = 12 AND bs.CreateTime>'2018-04-25 10:29:00'
 ) a WHERE a.cindex>1
 )

 

根据门店分组标记序号

          SELECT ROW_NUMBER() OVER(PARTITION BY bs.Phone,sl.Companyid ORDER BY  sl.id desc) AS cindex,
           bs.Phone,sl.* FROM TableXXXXX bs
 LEFT JOIN TableLLLLL sl
 ON sl.OrderId = bs.OrderId
 WHERE bs.OrderType = 12 AND bs.CreateTime>'2018-04-25 10:29:00'