如何在sql server查询中使用存储过程删除重复记录 [英] how to delete duplicate records by using stored procedure in sql server query

问题描述

我有一个搜索页面，如果我想搜索它会给出这里发送的内容搜索字段记录，当我输入日期时发送到日期n搜索它会给出重复记录这里我发送通知给两个公司有一个身份证，所以当我输入日期两个记录根据我向公司发送通知的数量，如果我发送给三家公司它将在搜索页面显示3次，但我想要一次输入日期发送和发送到日期...请给我解决方案...

i have a search page if i want to search it will give the content search field records here send from ,send to dates are there when i enter dates n search it will give duplicate records here one i sent notification to two companies with one id so it will give when i entered dates two records according to how many i sent notifications to companies if i sent to three companies it will show in search page 3 times but i want one time when enter dates send from and send to dates...please give to me solution...

推荐答案

从sql bible有一个解决方案我将复制&粘贴它



使用窗口删除重复的行



删除重复行的三种方法，这种方法最直接，因为它不需要改变表格或生成第二个表格。

这个的关键方法是使用带有aROW_NUMBER（）函数和

a分区的windowing'sOVER（）子句。分区将开始使用每个新分区重新编号。将theOVER（）子句设置为

PARTITION BYevery列，以检查重复数据。在这种情况下，每列都要检查

。

首先运行窗口查询显示它如何应用行号：

from sql bible there is a solution I will copy & paste it for you

Deleting duplicate rows using windowing

Of the three methods to remove duplicate rows, this method is the most straightforward because it
doesn''t need to alter the table or generate a second table.
The key to this method is using the windowing’sOVER()clause with aROW_NUMBER()function and
a partition. The partition will begin renumbering with every new partition. Set theOVER()clause to
PARTITION BYevery column to be checked for duplicate data. In this case, every column is being
checked.
Running the windowing query first shows how it applies the row number:

SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2ORDER BY Col1) AS rn
FROM DupsNoPK

结果：

Col1 Col2 rn

----------- ----- --------------------

1 abc 1

2 abc 1

2 abc 2

2 abc 3

7 xyz 1

7 xyz 2

每个重复行的anrnvalue大于1，所以现在很容易删除重复项：

Result:
Col1 Col2 rn
----------- ----- --------------------
1 abc 1
2 abc 1
2 abc 2
2 abc 3
7 xyz 1
7 xyz 2
Every duplicate row has anrnvalue of greater than 1, so it’s now easy to delete the duplicates:

WITH DupsNumbered
AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM DupsNoPK
)
DELETE DupsNumbered
WHERE rn > 1;

nextSELECT测试窗口效果删除重复查询：

The nextSELECTtests the effect of the windowing remove duplicates query:

SELECT Col1, Col2
FROM DupsNoPK;

结果：

Col1 Col2

----------- -----

1 abc

2 abc

7 xyz

Result:
Col1 Col2
----------- -----
1 abc
2 abc
7 xyz

我想在这个问题中你会问2个问题


1）在调用Select Query本身时避免重复记录。在您的选择查询本身中，您可以避免使用DISTINCT，GROUP BY显示重复值...



2）您在使用fromdate时询问某些日期和日期在你的SQL查询本身之间的todate之间。



如果你想在googleDistinct，Group By子句，在SQL之间对这个搜索进行更多的澄清。



那么你对此有一个想法..

I think in this question you ask arround 2 questions

1) Avoid duplicate records while calling the Select Query itself . In your select query itself you avoid to show that duplicate values using DISTINCT, GROUP BY...

2)your asking something from & to dates are you using "fromdate between todate" in your SQL query itself.

If you want more clariffication about this search in google "Distinct , Group By clause, between in SQL".

Then you get one idea about this..

最简单的技巧是，



1.

simplest trick is,

1.

select distinct * from tablename

2。

现在复制结果数据，

并放入excel表格



3.

使用

2.
now copy result data,
and put in excel sheet

3.
delete all data from your table using

Delete from tablename

现在右键单击表并打开它

复制已放入excel的过滤数据并粘贴到表格中



快乐编码！

：）

now right click table and open it
copy filtered data you have put in excel and paste in table

Happy Coding!
:)

这篇关于如何在sql server查询中使用存储过程删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！