oracle 没有重复的多列联合查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13937209/
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
Multiple column Union Query without duplicates
提问by Adam Halegua
I'm trying to write a Union Querywith multiple columns from two different talbes (duh), but for some reason the second column of the second Selectstatement isn't showing up in the output. I don't know if that painted the picture properly but here is my code:
我正在尝试Union Query使用来自两个不同 talbes (duh) 的多个列来编写一个，但是由于某种原因，第二个Select语句的第二列没有显示在输出中。我不知道这是否正确地绘制了图片，但这是我的代码：
Select empno, job
From EMP
Where job = 'MANAGER'
Union
Select empno, empstate
From EMPADDRESS
Where empstate = 'NY'
Order By empno
The output looks like:
输出看起来像：
EMPNO JOB
4600 NY
5300 MANAGER
5300 NY
7566 MANAGER
7698 MANAGER
7782 MANAGER
7782 NY
7934 NY
9873 NY
Instead of 5300 and 7782 appearing twice, I thought empstatewould appear next to jobin the output. For all other empno's I thought the values in the fields would be (null). Am I not understanding Unionscorrectly, or is this how they are supposed to work?
而不是 5300 和 7782 出现两次，我认为empstate会出现job在输出旁边。对于所有其他empno人，我认为字段中的值将是(null). 我是不是没有Unions正确理解，或者这是他们应该如何工作？
Thanks for any help in advance.
提前感谢您的任何帮助。
回答by Taryn
If you want the data in a separate column you will want a JOINnot a UNION:
如果您希望将数据放在单独的列中，您将需要一个JOINnot a UNION：
Select e.empno, e.job, a.empstate
From EMP e
left join EMPADDRESS a
on e.empno = a.empno
Where job = 'MANAGER'
AND empstate = 'NY'
Order By e.empno
A UNIONcombines the two results into a single set but the data is listed in the same columns. So basically they are placed on top of one another:
AUNION将两个结果合并为一个集合，但数据列在相同的列中。所以基本上它们被放置在彼此之上：
select col1, col2, 'table1' as src
from table1
union all
select col1, col2, 'table2' as src
from table2
Will result in:
将导致：
col1 | col2 | src
t1 | t1 | table1
t2 | t2 | table2
If you want to have the data in a separate column which is sounds like you do then you will use a join of the tables.
如果您想将数据放在一个单独的列中，这听起来像您所做的那样，那么您将使用表的连接。
回答by NYCdotNet
Bluefeet has the correct answer.
Bluefeet 有正确的答案。
Think of joins as combining tables horizontally - you're adding more columns to the original query with each table you join.
将连接视为水平组合表 - 您正在使用您连接的每个表向原始查询添加更多列。
Think of unions as stacking record sets vertically - you're adding extra rows to the same set of columns.
将联合视为垂直堆叠记录集 - 您正在向同一组列添加额外的行。
回答by StevieG
You need a JOIN for this..
你需要一个 JOIN 为此..
Select e.empno, e.job, ea.empstate
From EMP e LEFT OUTER JOIN EMPADDRESS ea ON e.empno = ea.empno
Where e.job = 'MANAGER'
And ea.empstate = 'NY'
Order By e.empno
UNION is for taking 2 result sets with the same column names and merging them into one. In your example, its lumping column 2 (job and empstate) together, and taking the name from the first select.
UNION 用于获取具有相同列名的 2 个结果集并将它们合并为一个。在您的示例中，将第 2 列（job 和 empstate）集中在一起，并从第一个选择中获取名称。
回答by DazzaL
i think you meant to write is as a join instead?
我想你的意思是写成一个连接？
ie if you wanted empstate to be null for those employee's not in NY.
即，如果您希望那些不在纽约的员工的 empstate 为空。
select empno, job, empstate
from emp e
left outer join empaddress a
on a.empno = e.empno
and e.empstate = 'NY'
where e.job = 'MANAGER';
回答by GKV
this one works in oracle..by using union ..here inner query will fetch out the all the columns after that grouping with empno and rest of the columns is string concatenated
这个在oracle中工作..通过使用union..here内部查询将在与empno分组之后取出所有列，其余列是字符串连接
select EMPNO
,wm_concat(job) job
,wm_concat(EMPSTATE) EMPSTATE
from
( select EMPNO,job,'' as EMPSTATE from EMP Where job ='MANAGER'
union select EMPNO,'' as job, EMPSTATE from EMPADDRESS Where empstate ='NY'
)
group by EMPNO order by 1
