在2个联合SQL Server表中获得不同的值 [英] Getting a distinct value across 2 union sql server tables

问题描述

我正在尝试使用联合来获取2个表中所有不同的值。

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

这个想法是要获取columnA列中所有唯一值的计数，而不用重复，这样我就可以得到包含唯一columnA的所有列的总和。

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.

这就是我尝试过的操作（SQL Server Express 2008）

This is what I tried (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
)

推荐答案

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

TableX和TableY字段上的额外区别没必要它们会在tmp.ColumnA子句中被剥夺。声明临时表应消除可能导致查询无法执行的含糊不清之处。

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.

这篇关于在2个联合SQL Server表中获得不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！