跨2个联合sql server表获取不同的值

I'm trying to get all distinct values across 2 tables using a union.

我正在尝试使用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
)

4 个解决方案

#1

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子句中。声明临时表应该消除可能阻止查询执行的歧义。

#2

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值。

#3

To get distinct values in Union query you can try this

要在Union查询中获取不同的值，您可以尝试这样做

Select distinct AUnion.Name,AUnion.Company from (SELECT Name,Company from table1 UNION SELECT Name,Company from table2)AUnion

#4

SELECT DISTINCT Id, Name
FROM   TableA
UNION ALL
SELECT DISTINCT Id, Name
FROM   TableB
WHERE  TableB.Id NOT IN (SELECT Id FROM TableA)

#1

SELECT COUNT(distinct tmp.ColumnA) FROM ( (SELECT ColumnA FROM TableX WHERE x=y) 
UNION (SELECT ColumnA FROM TableY WHERE y=z) ) as tmp