如何在PL/SQL中连接子查询？

Question

我需要在Oracle中连接子查询。这种方法不起作用，我得到了连接操作的语法错误。

select s1.key, s1.value, s2.value 
from ((select key, value
        from tbl 
        where id = 1) as s1
join 
    (select key, value
        from tbl 
        where id = 2) as s2
on s1.contract = s2.contract);

- mrt181

---

请明确提及连接类型，例如内连接、左外连接等。 - Zaki

请问您能否发布具体的错误信息呢？您正在使用不在内联视图中的“contract”列进行合并。 - Quassnoi

去掉最外层的括号并指定连接类型（内部）解决了问题。 - mrt181

Answer 1

在内联视图中，您应该选择正在连接的字段(contract)：

SELECT  s1.key, s1.value, s2.value 
FROM    (
        SELECT contract, key, value
        FROM   tbl 
        WHERE  id = 1
        ) as s1
JOIN    (
        SELECT  contract, key, value
        FROM    tbl 
        WHERE   id = 2
        ) as s2
ON     s1.contract = s2.contract

- Quassnoi

---

对我来说，我必须在表别名上删除 "as"：SELECT s1.key，s1.value，s2.value FROM（ SELECT contract，key，value FROM tbl WHERE id = 1 ）s1 JOIN（ SELECT contract，key，value FROM tbl WHERE id = 2 ）s2 ON s1.contract = s2.contract - Chris Ji

Answer 2

你的括号用多了。

你使用了过多的括号。

SELECT
  s1.key, 
  s1.value, 
  s2.value 
FROM (SELECT 
        key, 
        value
      FROM tbl 
      WHERE id = 1) AS s1
JOIN (SELECT 
        key, 
        value
      FROM tbl 
      WHERE id = 2) AS s2
  ON s1.contract = s2.contract;

- John Hartsock

Answer 3

去掉最外层的括号。

- Parris Varney