我有一个包含列Machine
、Product
和Sources
的表:
Machine | Product | Sources |
---|---|---|
M3 | H | cmdd6 |
M3 | H | 91 |
M3 | H | cmdd3 |
M4 | I | cmdd7 |
M4 | J | cmdd7 |
M4 | B | 827 |
M4 | B | cmdd7 |
在上表中,Machine
是M3,乘积相同,但Sources
列有多个进气口.因此,需要删除Sources
应始终以‘cmdd’为升序的重复行.
例如,如果产品有重复,而源不同,即‘cmdd6’或‘cmdd3’,则应删除重复行,而值将保留在源‘cmdd3’中.
下面是表中希望实现的结果
Machine | Product | Sources |
---|---|---|
M3 | H | cmdd3 |
M4 | I | cmdd7 |
M4 | J | cmdd7 |
M4 | B | cmdd7 |
下面是我试图删除Count>;1的值上的重复项的查询.
WITH CTE(Machine, Product,Sources, duplicatecount) AS
(
SELECT
Machine, Product, Sources,
ROW_NUMBER() OVER (PARTITION BY Machine, Product
ORDER BY Machine, Sources) AS DuplicateCount
FROM
Concatcleanup
)
DELETE FROM cte
WHERE duplicatecount > 1
如有任何帮助,我们将非常感激.