一起使用 SQL JOIN 和 UNION
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7082522/
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
Using SQL JOIN and UNION together
提问by nth
OK, I stumped as to why I'm getting the following behavior.
好的，我不明白为什么会出现以下行为。
I've got a "transaction header" table and "transaction detail" table. For a certain function, the detail table requires a bit of normalization to extract "Remark" data. Each detail record can have up to 3 remarks in it designated by the TranRemark1, TranRemark2 and TranRemark3 columns.
我有一个“交易标题”表和“交易明细”表。对于某个函数，明细表需要进行一些归一化来提取“备注”数据。每个详细记录最多可以有 3 个备注，由 TranRemark1、TranRemark2 和 TranRemark3 列指定。
I put together the following query thinking it would work, but it returns the incorrect number of records.
我将以下查询放在一起，认为它可以工作，但它返回的记录数不正确。
SELECT
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment
FROM
(select TranRemark1, TranID from TranDetail
union all
select TranRemark2, TranID from TranDetail
union all
select TranRemark3, TranID from TranDetail) AS a
LEFT JOIN TranHeader AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;
The result set I get is based on the number of TranHeader records that match the ClientName NOT the number of records that match the where clause from TranDetail. For example, if Client "Acme Inc." has 3 records in the header table and I use the above query for remark code "1234" (which matches only 1 record in TranDetail) the result set lists the correct record 3 times.
我得到的结果集基于与 ClientName 匹配的 TranHeader 记录数，而不是与 TranDetail 中的 where 子句匹配的记录数。例如，如果客户“Acme Inc.” 头表中有 3 条记录，我使用上述查询备注代码“1234”（仅匹配 TranDetail 中的 1 条记录）结果集列出了 3 次正确的记录。
EDITSo I'd expect from the above example to get a result set like this:
编辑所以我希望从上面的例子中得到这样的结果集：
ClientName--TranID--TranRemark1--TranDateOfService--TranPayment
Acme Inc ADC11 1234 8-16-2011 45.11
What I get is this:
我得到的是这样的：
ClientName--TranID--TranRemark1--TranDateOfService--TranPayment
Acme Inc ADC11 1234 8-16-2011 45.11
Acme Inc ADC11 1234 8-16-2011 45.11
Acme Inc ADC11 1234 8-16-2011 45.11
Keep in mind that there can be multiple records for a client in TranHeader.
请记住，TranHeader 中的客户端可以有多个记录。
I've tried right and full join, but it all comes out the same.
我尝试过正确和完全加入，但结果都是一样的。
Where am I missing the problem?
我在哪里错过了问题？
Thanks for the help.
谢谢您的帮助。
采纳答案by ypercube??
Can you try replacing:
你可以尝试更换：
LEFT JOIN TranHeader AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;
with:
和：
LEFT JOIN
( SELECT DISTINCT
TranId, ClientName
FROM TranHeader
) AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;
回答by Nathan Hughes
How about
怎么样
SELECT
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment
WHERE a.TranRemark1 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID
UNION ALL
SELECT
b.ClientName,
a.TranID
a.TranRemark2,
a.TranDateOfService,
a.TranPayment
WHERE a.TranRemark2 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID
UNION ALL
SELECT
b.ClientName,
a.TranID,
a.TranRemark3,
a.TranDateOfService,
a.TranPayment
WHERE a.TranRemark3 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID
?
?
I initially suggested
我最初建议
SELECT
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment,
a.TranRemark1,
a.TranRemark2,
a.TranRemark3
FROM
TranDetail a JOIN TranHeader As b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode
OR a.TranRemark2 = @RemarkCode
OR a.TranRemark3 = @RemarkCode;
but thought you probably want a separate line for each remark?
但您认为您可能需要为每个评论单独一行？
回答by user2063206
If doing a UNION ALL, then do a SELECT DISTINCT on the OUTER SQL. If doing a UNION, it will take care of the duplicates.
如果执行 UNION ALL，则在 OUTER SQL 上执行 SELECT DISTINCT。如果执行 UNION，它将处理重复项。
The UNION ALL with a DISTINCT gives better performance though I believe....
尽管我相信具有 DISTINCT 的 UNION ALL 会提供更好的性能....
回答by Peeyush Jain
use distinct while fetching data
获取数据时使用 distinct
SELECT distinct
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment
FROM`enter code here`
(select TranRemark1, TranID from TranDetail
union all
select TranRemark2, TranID from TranDetail
union all
select TranRemark3, TranID from TranDetail) AS a
LEFT JOIN TranHeader AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;
