oracle 并排连接两个 sql 查询，没有公共列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4408273/
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
Join two sql queries side by side with no column common
提问by Sharon
I need to join the results of two queries horizontally. Consider a query below which will return two rows:
我需要水平连接两个查询的结果。考虑下面的查询，它将返回两行：
Select *
from Salary
where sal > 10000
The result of the query above should be joined side by side with the result of the query below which will again return two rows.Or I need to just concatenate the two result sets:
上面查询的结果应该与下面查询的结果并排连接，这将再次返回两行。或者我只需要连接两个结果集：
Select 'xyz' from dual
union
Select 'abc' from dual
Please suggest how this can be done as I tried to do this with the query below but it returns a cartesian product:
请建议如何做到这一点，因为我试图用下面的查询来做到这一点，但它返回一个笛卡尔积：
Select *
from (Select *
from salary
where sal > 10000) TEMP1,
(Select 'xyz' from dual
union
Select 'abc' from dual) TEMP2
回答by Karl Bartel
You can do that by joining on rownum like this:
你可以通过像这样加入 rownum 来做到这一点：
SELECT *
FROM
(SELECT view_name, rownum AS r FROM all_views WHERE rownum <=10)
FULL OUTER JOIN (SELECT table_name, rownum AS r FROM all_tables WHERE rownum <=10) USING (r)
In your case this would look like (untested):
在您的情况下，这看起来像（未经测试）：
Select * from
(Select salary.*, rownum AS r from salary where sal>10000) TEMP1
FULL OUTER JOIN
(SELECT temp2.*, rownum r FROM
(Select 'xyz' from dual
union
Select 'abc' from dual) TEMP2)
USING (r)
回答by Sharon
Thank you very much for your help.
非常感谢您的帮助。
But what I need is a bit complicated. I have updated the previous query to be somewhat like below instead of simple query that I posted before (SELECT * FROM Salary WHERE sal > 10000):
但我需要的是有点复杂。我已经将之前的查询更新为有点像下面，而不是我之前发布的简单查询(SELECT * FROM Salary WHERE sal > 10000)：
SELECT name, sal, address
FROM (SELECT e1.name, s1.sal, s1.grade, s2.address FROM Emp e1, salary s1,
(Select empcode, address FROM Address WHERE empcode LIKE 'NY%') s2
WHERE e1.hiredate =201001
AND s1.sal>10000)
I know the above query does not make much of a relevance. However, this is similar to what I need actually. I am not posting the original one as that is very complicated but if this can be done for this query, then I will be able to replicate the same on the original one as well.
我知道上述查询没有多大意义。但是，这与我实际需要的相似。我不会发布原始的，因为它非常复杂，但如果可以针对此查询完成此操作，那么我也将能够在原始查询上复制相同的内容。
Thank you,
谢谢，
Sharon
沙龙
回答by Vincent Malgrat
you can introduce an artificial join column:
你可以引入一个人工连接列：
SELECT *
FROM (SELECT s.*, ROWNUM ID FROM Salary s WHERE sal > 10000) q1
JOIN (SELECT 'xyz' col1, 1 ID
FROM dual
UNION
SELECT 'abc' col1, 2 ID FROM dual) q2 ON q1.id = q2.id
