从 SQL 表中删除重复行(基于多列的值)
我有以下 SQL 表:
I have following SQL table:
AR_Customer_ShipTo
+--------------+------------+-------------------+------------+
| ARDivisionNo | CustomerNo | CustomerName | ShipToCode |
+--------------+------------+-------------------+------------+
| 00 | 1234567 | Test Customer | 1 |
| 00 | 1234567 | Test Customer | 2 |
| 00 | 1234567 | Test Customer | 3 |
| 00 | ARACODE | ARACODE Customer | 1 |
| 00 | ARACODE | ARACODE Customer | 2 |
| 01 | CBE1EX | Normal Customer | 1 |
| 02 | ZOCDOC | Normal Customer-2 | 1 |
+--------------+------------+-------------------+------------+
(ARDivisionNo, CustomerNo,ShipToCode) 构成该表的主键.
如果您注意到前 3 行属于同一客户(测试客户)，他们具有不同的 ShipToCode:1、2 和 3.第二个客户(ARACODE 客户)的情况类似.Normal Customer 和 Normal Customer-2 中的每一个都只有 1 条记录，带有一个 ShipToCode.
If you notice first 3 rows belong to same customer (Test Customer), who has different ShipToCodes: 1, 2 and 3. Similar is the case with second customer (ARACODE Customer). Each of Normal Customer and Normal Customer-2 has only 1 record with a single ShipToCode.
现在，我想在这个表上查询结果，每个客户只有 1 条记录.因此，对于任何有超过 1 条记录的客户，我希望保留 ShipToCode 值最高的记录.
Now, I would like to get result querying on this table, where I will have only 1 record per customer. So, for any customer, where there are more than 1 records, I would like to keep the record with highest value for ShipToCode.
我尝试了各种方法:
(1) 表中只有一条记录，我可以轻松获取客户列表.
(1) I can easily get the list of customers with only one record in table.
(2) 通过下面的查询，我能够得到所有客户的列表，他们在表中拥有多条记录.
(2) With following query, I am able to get the list of all the customers, who have more than one record in the table.
[Query-1]
SELECT ARDivisionNo, CustomerNo
FROM AR_Customer_ShipTo
GROUP BY ARDivisionNo, CustomerNo
HAVING COUNT(*) > 1;
(3) 现在，为了为上述查询返回的每条记录选择合适的 ShipToCode，我无法弄清楚如何遍历上述查询返回的所有记录.
(3) Now, in order to select proper ShipToCode for each record returned by above query, I am not able to figure out, how to iterate through all the records returned by above query.
如果我这样做:
[Query-2]
SELECT TOP 1 ARDivisionNo, CustomerNo, CustomerName, ShipToCode
FROM AR_Customer_ShipTo
WHERE ARDivisionNo = '00' and CustomerNo = '1234567'
ORDER BY ShipToCode DESC
然后我可以获取 (00-1234567-Test Customer) 的相应记录.因此，如果我可以在上面的查询 (query-2) 中使用来自 query-1 的所有结果，那么我可以为具有多条记录的客户获取所需的单条记录.这可以与点 (1) 的结果相结合，以实现所需的最终结果.
Then I can get the appropriate record for (00-1234567-Test Customer). Hence, if I can use all the results from query-1 in the above query (query-2), then I can get the desired single records for customers with more than one record. This can be combined with results from point (1) to achieve the desired end result.
同样，这比我遵循的方法更容易.请让我知道我该怎么做.
Again, this can be easier than approach I am following. Please let me know how can I do this.
[注意:我必须仅使用 SQL 查询来执行此操作.我不能使用存储过程，因为我将最终使用Scribe Insight"来执行这件事，它只允许我编写查询.]
[Note: I have to do this using SQL queries only. I cannot use stored procedures, as I am going to execute this thing finally using 'Scribe Insight', which only allows me to write queries.]
推荐答案
Sample SQL FIDDLE
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) 要删除记录，请使用删除查询而不是选择并将 Where 子句更改为 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;
相关文章