sql查询从表中删除仅一个重复的行 [英] sql query to delete only one duplicate row from table

问题描述

我有一张桌子，里面有一些重复的行.
我只想删除一个重复的行.
例如，我有9个重复的行，因此应仅删除一行，并应显示剩余的8行.

例子

Hi,
I have a table with some duplicate rows in it.
i want to delete only one duplicate row.
for example i have 9 duplicate rows so should delete only one row and should show 8 remaining rows.

example

                date         calling   called  duration    timestampp
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121
2012-06-19 10:22:45.000	165	218	155	1.9         121

从上述日期开始，应该只删除一行并显示3行

from above date should delete only one row and should show 3 rows

2012-06-19 10:22:45.000	165	218	155	1.9         100
2012-06-19 10:22:45.000	165	218	155	1.9         100
2012-06-19 10:22:45.000	165	218	155	1.9         100

从上述日期开始，应该只删除一行并显示2行

等.


谢谢与问候
DSVS

from above date should delete only one row and should show 2 rows

and etc..


Thanks and Regards
DSVS

推荐答案

请遵循以下文章中的建议:

http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml [ ^ ]

但是，不要删除每一个重复的行，而要遍历每行.如果它与上一行匹配并且您的布尔标志为false，则将其删除并将布尔标志设置为true.然后，如果下一行匹配，请检查布尔标志，并且由于它为true，所以不要删除该行.当一行与上一行不同时，将布尔标志设置为false.这将删除数据库中每个重复的行之一.

Follow the advice in the following article:

http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml[^]

However, instead of deleting every dulicate row, loop through each row. If it matches the previous row and your boolean flag is false, delete it and set a boolean flag to true. Then, if the next row matches, check the boolean flag and, since it is true, don''t delete that row. When a row is different than the previous row, set the boolean flag to false. This will delete one of each duplicate row in your database.

这将为您提供帮助

http://www.sqlteam.com/article/deleting-duplicate-records [ ^ ]

http://chiragrdarji.wordpress.com/2007/07/23/delete-single-row-from-duplicate-rows-in-sql-server-2005-and-2000/ [

This will help you

http://www.sqlteam.com/article/deleting-duplicate-records[^]

http://chiragrdarji.wordpress.com/2007/07/23/delete-single-row-from-duplicate-rows-in-sql-server-2005-and-2000/[^]

delete t
from   yourtable t
where  yourColumn = (select top 1 yourColumn from yourtable order by . . . )

这篇关于sql查询从表中删除仅一个重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！