使用SQL查询从多个条件中删除重复行

Question

我有一个列Machine,Product和Sources的表:

资源tbody> <<tr>

机器	产品
M3	H	cmdd6
M3	H	91
M3	H	cmdd3
M4	我	cmdd7
M4	J	cmdd7
M4 td>	827
M4 td>	cmdd7

您可以在ORDER BY子句ROW_NUMBER窗口函数中使用一个额外的手工字段，将">cmdd%" like值拉到所有其他值之上。

WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY Machine, Product
ORDER BY CASE WHEN Sources NOT LIKE 'cmdd%' THEN 1 END,
Sources
) AS DuplicateCount                 
FROM Concatcleanup
)
DELETE FROM cte
WHERE DuplicateCount > 1;

点击这里查看演示。

---

如果您想避免删除，您可以使用符号SELECT ... INTO <new_table> FROM ...并使用cte的代码:

SELECT Machine, Product, Sources,  
ROW_NUMBER() OVER(
PARTITION BY Machine, Product
ORDER BY CASE WHEN Sources NOT LIKE 'cmdd%' THEN 1 END,
Sources
) AS DuplicateCount
INTO newtab
FROM Concatcleanup;

点击这里查看演示。