SQL 对结果集进行分组排序过滤重复数据

时间：2021-12-03 02:43:51

简单的表操作：

select row_number() over(partition by A.gid order by A.gid  ) as RowN, A.* from Fit_Order A

关联表操作

select * from ( select   row_number() over(partition by GID order by gid ASC) as RowN, *
 from (select A.*,B.* from Fit_Order A INNER JOIN  Fit_OrderDetail  (nolock) B ON A.Gid=B.OrderGid ) AS BB where BB.OrderType='银联支付' ) TT where TT.RowN=1

With AS 子查询 递归CTE 方式

 WITH TemGid AS ( select  A.Gid  from Fit_Order(nolock) A  Where   A.OrderType='银联支付' ) SELECT  * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY BB.ShopPreGid ORDER BY BB.ShopPreGid ASC ) AS Row_Num,* 
                FROM( select  A.Gid AS ShopPreGid ,A.CreateDate, B.DetailStatus FROM ( SELECT * FROM Fit_Order(nolock)  WHERE Gid IN (SELECT * FROM TemGid )) A inner join  Fit_OrderDetail  (nolock) B ON A.Gid=B.OrderGid)AS BB WHERE BB.DetailStatus!='已完成'  ) TT WHERE  TT.Row_Num=1

 

标签：

相关文章