如何清除出在归档记录表重复条目 [英] How can I clear out duplicate entries in an archived records table

问题描述

一些背景信息：我工作的一个公司更新应用程序，我们必须在这几列，从标签不同，以文本框，下拉列表到一个gridview。在GridView的每一行是驻留在我的'入口'表不同的入门。当用户更改这些列中的一个（更新在下拉列表中选择的值，在文本框改变文本等），他们是点击一个更新按钮，然后插入被制作成我们的'EntryLog表中的变化。这就是我们如何归档到一个特定的条目进行的所有更改。问题是，旧的执行更新过程会为所有在GridView该页面的条目的档案记录，即使用户实际上只更新一个条目。这意味着，当用户更新一个条目，我们的'EntryLog'表中得到额外的档案记录，在现实中是相同的previous酮（意味着什么也没有实际改变为条目）

Some background info: I am working on updating an application for a company, and we have a gridview with several columns in it, which vary from labels, to textfields, to dropdowns. Each row in the gridview is for a different 'Entry' which resides in my 'Entry' Table. When a user changes one of these columns (updates the value selected in the dropdown, changes text in the textfield, etc) they WERE clicking an 'update button' which then inserted the change that was made into our 'EntryLog' table. This is how we archive all the changes made to a particular entry. The issue is, the old implementation of the update process would create an archive record for ALL of the entries in the gridview for that page, even when the user only actually updated ONE entry. This means that when a user updated one entry, our 'EntryLog' table was getting flooded with additional archive records that in reality were identical to the previous one (meaning nothing was actually changed for that entry)

走上了一个问题：我已经通过实施功能，只创造了在GridView单列已修改存档记录解决这个问题，但现在我留下清洁的任务我们的EntryLog所有这些虚假存档记录表。如果我有句话我试图用英文做的，我会说：我要删除所有日志（由LOGID [每个存档登录唯一ID]），其中每个列的值是一样的previous日志列值，并且具有相同的EntryID（唯一的ID为每个条目本身，这意味着有在'EntryLog表中的每个的EntryID的多个实例）。

Onto the question: I have already solved the issue by implementing functionality that only creates an archived record for the single row in the gridview that was modified, but now I am left with the task of cleaning out our 'EntryLog' table of all of those falsely archived records. If I had to phrase what I am trying to do in English I would say: I want to delete all of the Logs (by LogID [unique ID for each archive entry] ) where each column value is the same as the previous Log column values and have the same 'EntryID' (unique ID for each ENTRY itself, meaning there are many instances of each EntryID in the 'EntryLog' table. )

我不确定如何甚至开始在T-SQL语句安全这一点，任何帮助或在正确的方向轻移将大大AP preciated。

I am unsure how to even begin to safely phrase this in T-SQL and any help or nudge in the right direction would be greatly appreciated.

推荐答案

使用校验和（）功能，看数据是否一致，而不检查每一个列。

Use the CHECKSUM() function to see if the data is identical, without checking every single column.

假设你有这样的事情：

create table #t (log_id int, c varchar(10), d int, log_date date)

insert #t values
(1, 'aaaaa', 1, '20140101'),
(1, 'aaaaa', 1, '20140102'),
(1, 'aaaaa', 1, '20140103'),
(1, 'bbbbb', 1, '20140104'),
(2, 'ccc', 10, '20140103'),
(2, 'cdd', 10, '20140105')

在第二数据和第三行是重复的。要清洁数据，执行该语句：

The data in the second and the third row are duplicates. To clean the data, execute this statement:

with x as (
select *, row_number() over(partition by log_id, checksum(log_id, c, d) order by log_date) as rn
from #t
)
delete x where rn > 1

这篇关于如何清除出在归档记录表重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！