来自多个表的 SQL 查询结果没有重复 [英] SQL query result from multiple tables without duplicates
问题描述
我有许多表格，从所有记录中筛选出客户 ID、最后订单日期和该订单总金额、细分名称.每个过滤器都基于不同的标准，但是，相同的客户 ID 可以属于两个不同的表、两个不同的段.相同的 ID 在 Last Order 和 Total 中会有不同的值.段，表名是A、B、C、D.
I have a number of tables with filtered from all the records customer ID's, Last Order Date and that order Total $, Segment Name. Each filter is based on different criteria but, same customer ID can belong two different tables, two different segments. Same ID would have different values in Last Order and Total in . Segments, table names are A, B, C, D.
我需要以集合中没有重复 ID 的方式对所有段表中的记录进行分组.即:如果一个 ID 出现在多个表中(比如 ID 2 在表 A 和 B 中)，结果集必须显示来自第一个表，表 A 的 ID 列.
I need to group the records from All the segment tables in a way that there are no duplicate ID's in the set. i.e.: if an ID appears in more than one table (say ID 2 is in tables A and B) the result set has to be showing ID columns from the first table, table A.
所以我需要列出 Segment A 表中的所有记录及其列值，列出 Segment B 表中的所有记录及其值，除非 Segment B 表中的任何 ID 位于 Segment A 中，并且列出所有来自 Segment C 表的记录，除非来自 Segment C 的 ID 在 Segment A 或 B 表中.我希望它是有道理的.
So I need to list of all the records and their column values from Segment A table, list of all the records and its values from Segment B table except if any ID in Segment B table is in Segment A and list of all the records from Segment C table except if ID from Segment C are in Segment A or B table . I hope it does makes sense.
我让它听起来像是 70-461 考试中的一个问题 :D 我已经对它进行了相当彻底的研究，但也许我不知道如何提出这些问题.我想知道是否有人会知道如何构建查询以获得该结果.非常感谢您的任何建议.
I made it sound like a question from 70-461 exam :D I've researched it quite thoroughly but perhaps I don't see how to ask that questions. I wonder if anyone would have idea of how to build a query to get that result. Big thanks for any suggestions.
谢谢各位.我似乎无法发布屏幕截图.让我尝试通过 html 输入它.有更多段表，但只需输入两个即可给您一个想法.谢谢各位！
Thanks guys. I couldn't seem to post a screenshot. Let me try to type it via html. There are more segment tables but just typing two to give you an idea. Thanks guys!
Segment A
----------------------------------------
ID | Last Order Date | Total | Segment
----------------------------------------
1 | 01/01/2012 | $1 | A
----------------------------------------
2 | 01/01/2012 | $1 | A
----------------------------------------
3 | 01/01/2012 | $5 | A
----------------------------------------
6 | 01/01/2012 | $7 | A
----------------------------------------
8 | 01/01/2012 | $8 | A
Segment B
ID | Last Order Date | Total | Segment
--------------------------------------
4 | 01/01/2010 | $3 | B
--------------------------------------
2 | 01/01/2010 | $5 | B
--------------------------------------
1 | 01/01/2010 | $2 | B
--------------------------------------
3 | 01/01/2010 | $1 | B
--------------------------------------
5 | 01/01/2010 | $7 | B
Result Set
ID | Last Order Date | Total | Segment
--------------------------------------
1 | 01/01/2012 | $1 | A
--------------------------------------
2 | 01/01/2012 | $1 | A
--------------------------------------
3 | 01/01/2012 | $5 | A
--------------------------------------
4 | 01/01/2010 | $3 | B
--------------------------------------
5 | 01/01/2010 | $7 | B
推荐答案
这里有一些东西可以帮助您入门:
Here's something to get you started:
SELECT ID, LastOrderDate, Total, Segment
FROM SegmentA
UNION ALL
SELECT ID, LastOrderDate, Total, Segment
FROM SegmentB
WHERE ID NOT IN (SELECT ID FROM SegmentA)
UNION ALL
SELECT ID, LastOrderDate, Total, Segment
FROM SegmentC
WHERE ID NOT IN (SELECT ID FROM SegmentA)
AND ID NOT IN (SELECT ID FROM SegmentB)
UNION ALL
SELECT ID, LastOrderDate, Total, Segment
FROM SegmentD
WHERE ID NOT IN (SELECT ID FROM SegmentA)
AND ID NOT IN (SELECT ID FROM SegmentB)
AND ID NOT IN (SELECT ID FROM SegmentC)
一个非常简单的答案，如果您想优化它，则需要更多信息.
A very simplistic answer, more information is needed if you want to optimize this.
这篇关于来自多个表的 SQL 查询结果没有重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
