SQL sql从select插入表而没有重复（需要更多然后一个DISTINCT）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5916667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql insert into table from select without duplicates (need more then a DISTINCT)
提问by tdjfdjdj
I am selecting multiple rows and inserting them into another table. I want to make sure that it doesn't already exists in the table I am inserting multiple rows into. DISTINCTworks when there are duplicate rows in the select, but not when comparing it to the data already in the table your inserting into.
我正在选择多行并将它们插入到另一个表中。我想确保它不存在于我要插入多行的表中。 DISTINCT在选择中有重复的行时有效，但在将其与插入的表中已有的数据进行比较时无效。
If I Selected one row at a time I could do a IF EXISTbut since its multiple rows (sometimes 10+) it doesn't seem like I can do that.
如果我一次选择一行，我可以执行IF EXIST但由于它的多行（有时是 10+），我似乎无法做到这一点。
采纳答案by tdjfdjdj
There are a few MSDN articles out there about this, but by far this one is the best:
MSDN 上有几篇关于这个的文章，但到目前为止，这是最好的一篇：
http://msdn.microsoft.com/en-us/library/ms162773.aspx
http://msdn.microsoft.com/en-us/library/ms162773.aspx
They made it real easy to implement and my problem is now fixed. Also the GUI is ugly, but you actually can set minute intervals without using the command line in windows 2003.
他们让实施变得非常容易，我的问题现在已经解决了。GUI也很丑，但实际上您可以在不使用Windows 2003中的命令行的情况下设置分钟间隔。
回答by a_horse_with_no_name
INSERT INTO target_table (col1, col2, col3)
SELECT DISTINCT st.col1, st.col2, st.col3
FROM source_table st
WHERE NOT EXISTS (SELECT 1
FROM target_table t2
WHERE t2.col1 = st.col1
AND t2.col2 = st.col2
AND t2.col3 = st.col3)
If the distinct should only be on certain columns (e.g. col1, col2) but you need to insert all column, you will probably need some derived table (ANSI SQL):
如果不同的应该只在某些列（例如 col1、col2）上，但您需要插入所有列，您可能需要一些派生表（ANSI SQL）：
INSERT INTO target_table (col1, col2, col3)
SELECT st.col1, st.col2, st.col3
FROM (
SELECT col1,
col2,
col3,
row_number() over (partition by col1, col2 order by col1, col2) as rn
FROM source_table
) st
WHERE st.rn = 1
AND NOT EXISTS (SELECT 1
FROM target_table t2
WHERE t2.col1 = st.col1
AND t2.col2 = st.col2)
回答by Xavier Holt
If you already have a unique index on whatever fields need to be unique in the destination table, you can just use INSERT IGNORE(here's the official documentation- the relevant bit is toward the end), and have MySQL throw away the duplicates for you.
如果您已经在目标表中需要唯一的任何字段上拥有唯一索引，则可以使用INSERT IGNORE（这是官方文档- 相关部分即将结束），并让 MySQL 为您丢弃重复项。
Hope this helps!
希望这可以帮助！
回答by Jaymz
So you're looking to retrieve all unique rows from source table which do not already exist in target table?
因此，您希望从源表中检索目标表中尚不存在的所有唯一行？
SELECT DISTINCT(*) FROM source
WHERE primaryKey NOT IN (SELECT primaryKey FROM target)
That's assuming you have a primary key which you can base the uniqueness on... otherwise, you'll have to check each column for uniqueness.
那是假设你有一个主键，你可以基于它的唯一性……否则，你必须检查每一列的唯一性。
回答by derekcohen
pseudo code for what might work
可能有效的伪代码
insert into <target_table> select col1 etc
from <source_table>
where <target_table>.keycol not in
(select source_table.keycol from source_table)
