删除或忽略多个字段重复的行

Question

我的数据来自电力供应商。有些费用是固定的，有些是使用费。我住在一个问题固定收费。当电表在计费期间发生变化时，创建两条固定收费线路，分别使用不同的计量号和合同号。其他所有领域都是相同的，我想得到其中一个。因为是按月固定收费。

如果你帮助我，我会很高兴。非常感谢，

https://www.designcise.com/web/tutorial/how-to-remove-all-duplicate-rows-except-one-in-sql#:~:text=How%20to%20Remove%20All%20Duplicate%20Rows%20Except%20One,Duplicates%20and%20Keep%20Row%20With%20Highest%20ID%20我创建了一个没有这两个字段的视图，并获得唯一字段，并创建了另一个视图，并添加了这两个字段，给出了比实际值小的值进行比较。第二个视图的值(A10000000'作为MeterUniqueNo， '100000'作为metercontracactid)原始示例K18D01652, 646802)

delete from main_table 
Inner join view2 on view2.MeterUniqueNo < main_table.MeterUniqueNo
and view2.EnergyChargesRecord_InvoiceNumber = main_table.EnergyChargesRecord_InvoiceNumber
and view2.EnergyChargesRecord_MPANNumber = main_table.EnergyChargesRecord.MPANNumber

不工作，因为值不同。

T-SQL:删除所有重复的行但保留一个

我不能用这个方法。因为我要核对MPAN号和发票号。不只一个值…

Answer 1

由于您没有提供任何示例数据，所以我猜测您的数据的实际格式。我创建了一个最小的示例，说明如何使用ROW_NUMBER函数对重复项进行排序，并只选择最近的一个。同样，我猜测样本数据，但重复行之间的公共数据是MPAN_number列。这只是一个示例，提供示例数据以获得更好的或更具体到您的应用程序的答案。

--Create test table.
CREATE TABLE charges (
meter_id int
, contract_number int 
, charge_amt decimal(19,2)
, invoice_number int
, MPAN_number nvarchar(100)
, charge_date date
);
--Insert test data.
INSERT INTO charges (
meter_id, contract_number, charge_amt, invoice_number, MPAN_number
, charge_date)
VALUES 
(123, 998, 25.54, 3216549, '123AM234ASF', '1/2/2022')
, (456, 12399, 25.54, 3216668, '123AM234ASF', '1/15/2022')
, (987, 887, 25.54, 3589765, 'K18D01652', '1/5/2022')
, (654, 123488, 25.54, 3548892, 'K18D01652', '1/28/2022')
;
--For debugging, show all test data.
SELECT * FROM charges;
--Use a CTE to add a row_num column. 
--This row_num column will sequence "duplicate" charge lines by charge_date with the most recent charge as row_num = 1.
--The common data in the example data is the MPAN_number.
--This is only an example, for more specific help, you need to create
--a mimimal reproducible example just like this.
WITH prelim as (
SELECT *, ROW_NUMBER() OVER(PARTITION BY MPAN_number ORDER BY charge_date DESC) as row_num
FROM charges
)
SELECT *
FROM prelim
WHERE row_num = 1
;
--Here's an example of how to delete all "duplicate" charges that are not the most recent charge.
DELETE c 
FROM charges as c
INNER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY MPAN_number ORDER BY charge_date DESC) as row_num
FROM charges
) as oldDups
ON oldDups.MPAN_number = c.MPAN_number
AND oldDups.meter_id = c.meter_id
AND oldDups.contract_number = c.contract_number
AND oldDups.row_num <> 1
;
--For debugging, show the test data after deletions.
SELECT * FROM charges;

显示所有测试数据:

charge_date2022-01-022022-01-152022-01-052022-01-28