更新时间:2023-02-02 21:06:11
1)使用CTE根据ARDivisionNo,CustomerNo
为每个客户获取最大船舶代码值记录
1) Use CTE to get max ship code value record based on ARDivisionNo, CustomerNo for each Customers
WITH cte AS (
SELECT*,
row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
FROM t
)
Select * from cte WHERE [rn] = 1
2)删除记录使用删除查询而不是选择并将其中的条款更改为rn> 1. 示例SQL FIDDLE
2) To Delete the record use Delete query instead of Select and change Where Clause to rn > 1. Sample SQL FIDDLE
WITH cte AS (
SELECT*,
row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
FROM t
)
Delete from cte WHERE [rn] > 1;
select * from t;