如何删除该特定语句中的重复行

Question

我已经看到了一些删除重复行的好例子，但是我的尝试失败了。这里面有某种东西不会让它发生。错误不会告诉我任何东西来帮助我知道它出了什么问题。我就不把它加进去了，免得把我的问题弄脏了。假设这个脚本可以工作，并为每个重复行提供一个顺序索引，如DuplicateCount。基本上所有带有DuplicateCount>我需要删除。我怎么把这个和这个语句括起来呢?

SELECT
v.WONum,
o.OrderNumber,
o.GUIDOrder,
v.WorkOrder,
v.Product,
v.BuildDate,
v.TestDate,
v.PassFail,
v.Valve,
ROW_NUMBER() OVER
(Partition BY v.WONum, o.OrderNumber, o.GUIDOrder, v.WorkOrder, v.Product, v.BuildDate, v.TestDate, v.PassFail, v.Valve
ORDER BY Index_VSN) AS DuplicateCount
FROM dbo.Valve_One_Worksheet_New AS v 
LEFT OUTER JOIN [KT-RD-01acctivate].ACCTivate$KELSOTECH.dbo.Orders AS o 
ON v.WONum COLLATE SQL_Latin1_General_CP1_CI_AS = o.OrderNumber COLLATE SQL_Latin1_General_CP1_CI_AS

我试过了，没有效果。

https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

Answer 1

嗯…我想我想得太复杂了。我确实需要拿出DuplicateCount之类的东西……这就是最终成功的方法。谢谢,@FelixACR

SELECT DISTINCT v.WONum, o.OrderNumber, o.GUIDOrder, v.WorkOrder, v.Product, v.BuildDate, v.TestDate, v.PassFail, v.Valve
FROM   dbo.Valve_One_Worksheet_New AS v LEFT OUTER JOIN
[KT-RD-01acctivate].ACCTivate$KELSOTECH.dbo.Orders AS o ON v.WONum COLLATE SQL_Latin1_General_CP1_CI_AS = o.OrderNumber COLLATE SQL_Latin1_General_CP1_CI_AS

Answer 2

你试过SELECT DISTINCT吗?它的正常用途是删除重复的行。

SELECT DISTINCT v.WONum, o.OrderNumber, o.GUIDOrder, v.WorkOrder, v.Product, v.BuildDate, v.TestDate, v.PassFail, v.Valve, ROW_NUMBER() OVER (Partition BY v.WONum, o.OrderNumber, o.GUIDOrder, v.WorkOrder, v.Product, v.BuildDate, v.TestDate, v.PassFail, v.Valve
ORDER BY Index_VSN) AS DuplicateCount FROM dbo.Valve_One_Worksheet_New AS v 
LEFT OUTER JOIN [KT-RD-01acctivate].ACCTivate$KELSOTECH.dbo.Orders AS o 
ON v.WONum COLLATE SQL_Latin1_General_CP1_CI_AS = o.OrderNumber COLLATE SQL_Latin1_General_CP1_CI_AS