从查询中删除重复项，但不从表中删除重复项 [英] Removing duplicates from query, but not from table
问题描述
我现在一直在搜索如何从Access数据库中的
表中删除重复内容并找到大量有关查找
或删除的文章重复。我想做的就是从
SQL查询中删除它们 - 当然留下其中一条记录。
我有一个包含的邮件列表一个联合查询，从
两个单独的表中获取记录。
我希望能够运行一个查询，删除一个（或多个）
查询中的重复地址，但没有对表格进行任何修改。
请帮忙！ :)
I''ve been searching for awhile now on how to remove duplicates from a
table within an Access db and have found plenty of articles on finding
or deleting duplicates. All I want to do is remove them from within an
SQL query - leaving one of the records behind of course.
I have a mailing list comprised of a union query that gets records from
two separate tables.
I want to be able to run a query that removes one (or more) of the
duplicated addresses within the query but doesn''t make any
modifications to the table.
Please help! :)
推荐答案
说你要删除重复的地址真的没有意义
来自查询而不修改表。查询只是一种显示表格中的内容的方式，因此通过查询删除的任何内容都会从
表中删除。如果你想要你可以将你的unioin查询的结果放入一个新的
表（使它成为一个make table查询），然后你可以从该表中删除重复的
（通过查询）而无需修改原始表格。
希望有帮助
-John
" tyrfboard" < HT ***** @ gmail.com>在消息中写道
news：11 ********************* @ g43g2000cwa.googlegro ups.com ...
It doesn''t really make sense to say you want to delete duplicate addresses
from a query without modifying a table. A query is just a way of displaying
what''s in a table, so anything you delete via a query is deleted from the
table. If you want you could put the results of your unioin query into a new
table (make it a make table query), and then you could remove duplicates
from that table (via a query) without modifying the original tables.
hope that helped
-John
"tyrfboard" <ht*****@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
我现在一直在搜索如何从Access数据库中的
表中删除重复项，并找到大量有关查找
或删除重复项的文章。我想要做的就是从一个
SQL查询中删除它们 - 当然留下其中一个记录。
我有一个邮件列表，包含一个联合查询，它从
两个单独的表。
我希望能够运行一个查询，删除查询中的一个（或多个）重复地址，但不做任何修改到桌子。
请帮忙！ ：）
I''ve been searching for awhile now on how to remove duplicates from a
table within an Access db and have found plenty of articles on finding
or deleting duplicates. All I want to do is remove them from within an
SQL query - leaving one of the records behind of course.
I have a mailing list comprised of a union query that gets records from
two separate tables.
I want to be able to run a query that removes one (or more) of the
duplicated addresses within the query but doesn''t make any
modifications to the table.
Please help! :)
" tyrfboard" < HT ***** @ gmail.com>在消息中写道
news：11 ********************* @ g43g2000cwa.googlegro ups.com ...
"tyrfboard" <ht*****@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
我现在一直在搜索如何从Access数据库中的
表中删除重复项，并找到大量有关查找
或删除重复项的文章。我想要做的就是从一个
SQL查询中删除它们 - 当然留下其中一个记录。
我有一个邮件列表，包含一个联合查询，它从
两个单独的表。
我希望能够运行一个查询，删除查询中的一个（或多个）重复地址，但不做任何修改到桌子。
请帮忙！ ：）
I''ve been searching for awhile now on how to remove duplicates from a
table within an Access db and have found plenty of articles on finding
or deleting duplicates. All I want to do is remove them from within an
SQL query - leaving one of the records behind of course.
I have a mailing list comprised of a union query that gets records from
two separate tables.
I want to be able to run a query that removes one (or more) of the
duplicated addresses within the query but doesn''t make any
modifications to the table.
Please help! :)
我不完全清楚你要做什么，但是......一个UNION查询
将由默认情况下，消除成员查询之间的重复记录。
如果你想包括所有记录，无论重复，你都需要使用UNION ALL。
需要使用UNION ALL。
如果您从成员查询中获得重复项，那么您需要的是DISTINCT限定符。
SELECT DISTINCT
field1，field2，field3
FROM
table1
UNION
SELECT DISTINCT
field1，field2，field3
FROM
table2
此语法应该消除所有从你的查询输出重复。
-
兰迪哈里斯
tech在promail dot com
我很确定我知道我记得的一切。
I''m not completely clear on what you are trying to do, but.. A UNION query
will, by default, eliminate duplicate records between the member queries.
If you wanted to include all records, regardless of duplicates, you would
need to use UNION ALL.
If you are getting duplicates from within the member queries, then what you
need is the DISTINCT qualifier.
SELECT DISTINCT
field1, field2, field3
FROM
table1
UNION
SELECT DISTINCT
field1, field2, field3
FROM
table2
This syntax should eliminate all of the duplicates from you query output.
--
Randy Harris
tech at promail dot com
I''m pretty sure I know everything that I can remember.
感谢您的回复......让我试试解释我正在做什么和
也许这将有助于我获得最好的专家建议。
例如：让我说我有两张桌子与姓名，地址等：
个人表
工作表
我有一个联合查询，合并了从个人和工作领域
一起到一个邮件列表。
然而，有些人属于个人和工作
列表 - 但我不想两次向这些人发送邮件。
如果这些记录中的每个字段完全相同，那么选择
distinct可以很好地工作。问题是，重复可能像
这个：
FirstName |姓氏| HomeNum | HomeStreet
----------------------------------------- ----
Jonah |史密斯| 1983年| Dovecrest Lane（来自工作）
Jon |史密斯| 1983年| Dovecrest（来自个人）
所以我希望有一个查询可以根据匹配的LastName和HomeNum字段取消选择其中一行
。并且，如果
可能，我希望保留来自Work
表的行更频繁地更新。
Thank you for your replies.... let me try to explain what I''m doing and
maybe that will help me get the best of your expert advice.
For example: Lets say I have two tables with names, addresses, etc:
Personal table
Work table
I''ve got a Union query that merges the fields from Personal and Work
together into one mailing list.
However, there are people who belong on both the personal and work
lists - but I don''t want to send mail to those individuals twice.
If each field in these records were exactly the same, then select
distinct would work beautifully. Problem is, the dups might be like
this:
FirstName | LastName | HomeNum | HomeStreet
---------------------------------------------
Jonah | Smith | 1983 | Dovecrest Lane (from work)
Jon | Smith | 1983 | Dovecrest (from personal)
So I would like to have a query that would deselect one of those lines
based on the LastName and HomeNum fields matching up. AND, if
possible, I''d like for the line that is kept to come from the Work
table as that is updated more often.
这篇关于从查询中删除重复项，但不从表中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
