sql-server - CTE 删除重复数据

Question

我有一个重复行的表。我想删除重复项并添加一个复合键以避免重复项。

;WITH myCTE (RowNumber, invoice_id, Invoice_Number, Organization_id, status, created_at) AS 
( 
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Invoice_Number ORDER BY invoice_id,  Invoice_Number DESC) AS RowNumber , 
        invoice_id, Invoice_Number, Organization_id,status, created_at 
    FROM  
        Invoice_Export
) 
SELECT * 
FROM myCTE 
WHERE Invoice_number LIKE '%-00%' 
ORDER BY invoice_id

select * from Invoice_Export

ALTER TABLE Invoice_Export ALTER COLUMN [Organization_id] NVARCHAR(36) NOT NULL
ALTER TABLE Invoice_Export ALTER COLUMN [Invoice_Number] NVARCHAR(15) NOT NULL

ALTER table Invoice_Export
ADD CONSTRAINT [Composite_Key_Invoice] PRIMARY KEY CLUSTERED (Organization_id, Invoice_Number)

有没有其他更好的方法。

标签： sql-serverduplicatescommon-table-expression

Answer 1

我猜 'invoice_id' 是独一无二的，所以

DELETE FROM Invoice_Export duplicate
WHERE EXISTS (SELECT 1 FROM Invoice_Export
  WHERE duplicate.invoice_id > Invoice_Export.invoice_id
  AND duplicate.Organization_id = Invoice_Export.Organization_id
  AND duplicate.Invoice_Number = Invoice_Export.Invoice_Number)

相反PRIMARY KEY，您可以使用UNIQUE（删除重复项后）

ALTER TABLE Invoice_Export
ADD CONSTRAINT UC_Invoice_Export UNIQUE (Organization_id, Invoice_Number);