在 2 个联合 sql server 表中获取不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1891016/
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
Getting a distinct value across 2 union sql server tables
提问by rockit
I'm trying to get all distinct values across 2 tables using a union.
我正在尝试使用联合在 2 个表中获取所有不同的值。
The idea is to get a count of all unique values in the columnA column without repeats so that I can get a summation of all columns that contain a unique columnA.
这个想法是在不重复的情况下获取 columnA 列中所有唯一值的计数，以便我可以获得包含唯一 columnA 的所有列的总和。
This is what I tried (sql server express 2008)
这是我尝试过的（sql server express 2008）
select
count(Distinct ColumnA)
from
(
select Distinct ColumnA as ColumnA from tableX where x = y
union
select Distinct ColumnA as ColumnA from tableY where y=z
)
回答by Jim Dagg
SELECT COUNT(distinct tmp.ColumnA) FROM ( (SELECT ColumnA FROM TableX WHERE x=y)
UNION (SELECT ColumnA FROM TableY WHERE y=z) ) as tmp
The extra distincts on TableX and TableY aren't necessary; they'll get stripped in the tmp.ColumnA clause. Declaring a temporary table should eliminate the ambiguity that might've prevented your query from executing.
TableX 和 TableY 上的额外区别不是必需的；它们将在 tmp.ColumnA 子句中被剥离。声明一个临时表应该可以消除可能阻止您的查询执行的歧义。
回答by AdaTheDev
SELECT COUNT(*)
FROM
(
SELECT DISTINCT ColumnA From TableX WHERE x = y
UNION
SELECT DISTINCT ColumnA From TableY WHERE y = z
) t
Using a "UNION" will not return duplicates. If you used "UNION ALL" then duplicate ColumnA values from each table WOULD be return.
使用“UNION”不会返回重复项。如果您使用“UNION ALL”，则将返回每个表中重复的 ColumnA 值。
回答by Prateek Gupta
To get distinct values in Union query you can try this
要在联合查询中获得不同的值，您可以尝试这个
Select distinct AUnion.Name,AUnion.Company from (SELECT Name,Company from table1 UNION SELECT Name,Company from table2)AUnion
回答by Everaldo Carneiro
SELECT DISTINCT Id, Name
FROM TableA
UNION ALL
SELECT DISTINCT Id, Name
FROM TableB
WHERE TableB.Id NOT IN (SELECT Id FROM TableA)
