I have a loooooong stored procedure that runs about 15 select statements across different tables in a database - I am inserting all the results of the selects into a temporary table. However - that table has some rows that are exact duplicates. The temporary table looks something like this:
我有一个loooooong存储过程，它在数据库中的不同表中运行大约15个select语句 - 我将所有选择的结果插入到临时表中。但是 - 该表有一些完全重复的行。临时表看起来像这样：
DocID | VisitDate | DocName
8245376 | 8/26/2009 1:07:01 PM | Doc1
8245376 | 8/26/2009 1:07:01 PM | Doc1
8245376 | 8/26/2009 1:07:01 PM | Doc2
8245376 | 8/26/2009 1:07:01 PM | Doc2
646681 | 8/26/2009 1:07:01 PM | Doc3
263272 | 8/26/2009 1:07:01 PM | Doc4
8245376 | 8/26/2009 1:07:01 PM | Doc5
8245376 | 8/26/2009 1:07:01 PM | Doc5
8245376 | 8/26/2009 1:07:01 PM | Doc6
8245376 | 8/26/2009 1:07:01 PM | Doc6
1903875 | 8/26/2009 1:07:01 PM | Doc7
And how I'd like the table to look at the end is like this:
而我希望表格看到最后是这样的：
DocID | VisitDate | DocName
8245376 | 8/26/2009 1:07:01 PM | Doc1
8245376 | 8/26/2009 1:07:01 PM | Doc2
646681 | 8/26/2009 1:07:01 PM | Doc3
263272 | 8/26/2009 1:07:01 PM | Doc4
8245376 | 8/26/2009 1:07:01 PM | Doc5
8245376 | 8/26/2009 1:07:01 PM | Doc6
1903875 | 8/26/2009 1:07:01 PM | Doc7
How can I return only ONE row if there are multiple duplicate rows and still return rows that are not duplicates?
如果有多个重复的行仍然返回不重复的行，我怎么能只返回一行？
4 个解决方案
#1
9
SELECT DISTINCT DocID, VisitDate, DocName
FROM mytable
Or I am missing something?
或者我错过了什么？
Update:
更新：
If you have control over the stored procedure, you can rewrite it so that no duplicates will ever get into the table.
如果您可以控制存储过程，则可以重写它，以便不会重复进入表中。
Assuming DocID is a PRIMARY KEY, declare it as such in the temporary table using IGNORE_DUP_KEY:
假设DocID是PRIMARY KEY，请使用IGNORE_DUP_KEY在临时表中声明它：
DECLARE @temp TABLE (DocId INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON), …)
INSERT
INTO @mytable
SELECT …
FROM source_table
This will skip duplicates on DocID
这将跳过DocID上的重复项
#2
2
If the duplicates are coming from the source tables, then do a SELECT DISTINCT when you are doing an insert into your temp tables.
如果重复项来自源表，则在对临时表进行插入时执行SELECT DISTINCT。
If the duplicates are coming across tables, then just do a SELECT DISTINCT from your temp table after you have inserted all the rows.
如果重复项是跨表，那么在插入所有行后，只需从临时表中执行SELECT DISTINCT。
Trying to actually delete rows from a table that are duplicate is a bit more involved, but doesn't seem to be necessary here, because you are working with a temp table.
尝试实际删除表中重复的行更复杂，但这里似乎没有必要，因为您正在使用临时表。
#3
2
Try SELECT DISTINCT instead of SELECT. DISTINCT keyword will remove the duplicate value
尝试SELECT DISTINCT而不是SELECT。 DISTINCT关键字将删除重复值
#4
0
If you want to eliminate the duplicates from the temp table or better yet, don't put them in there in the first place, turn you multiple selects into a larger union query and insert that result into your temp table.
如果要从临时表中删除重复项或更好，请不要将它们放在那里，将多个选择转换为更大的联合查询并将该结果插入到临时表中。
#1
9
SELECT DISTINCT DocID, VisitDate, DocName
FROM mytable
Or I am missing something?
或者我错过了什么？
Update:
更新：
If you have control over the stored procedure, you can rewrite it so that no duplicates will ever get into the table.
如果您可以控制存储过程，则可以重写它，以便不会重复进入表中。
Assuming DocID is a PRIMARY KEY, declare it as such in the temporary table using IGNORE_DUP_KEY:
假设DocID是PRIMARY KEY，请使用IGNORE_DUP_KEY在临时表中声明它：
DECLARE @temp TABLE (DocId INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON), …)
INSERT
INTO @mytable
SELECT …
FROM source_table
This will skip duplicates on DocID
这将跳过DocID上的重复项
#2
2
If the duplicates are coming from the source tables, then do a SELECT DISTINCT when you are doing an insert into your temp tables.
如果重复项来自源表，则在对临时表进行插入时执行SELECT DISTINCT。
If the duplicates are coming across tables, then just do a SELECT DISTINCT from your temp table after you have inserted all the rows.
如果重复项是跨表，那么在插入所有行后，只需从临时表中执行SELECT DISTINCT。
Trying to actually delete rows from a table that are duplicate is a bit more involved, but doesn't seem to be necessary here, because you are working with a temp table.
尝试实际删除表中重复的行更复杂，但这里似乎没有必要，因为您正在使用临时表。
#3
2
Try SELECT DISTINCT instead of SELECT. DISTINCT keyword will remove the duplicate value
尝试SELECT DISTINCT而不是SELECT。 DISTINCT关键字将删除重复值
#4
0
If you want to eliminate the duplicates from the temp table or better yet, don't put them in there in the first place, turn you multiple selects into a larger union query and insert that result into your temp table.
如果要从临时表中删除重复项或更好，请不要将它们放在那里，将多个选择转换为更大的联合查询并将该结果插入到临时表中。