select A.ID, A.NAME, B.ID, B.AGE
from (select 1 id, 'AAA' name
from dual
union
select 2 id, 'BBB' name
from dual
union
select 3 id, 'CCC' name
from dual
union
select 1 id, 'DDD' name from dual) A
left join
(select 1 id, 23 age
from dual
union
select 2 id, 24 age
from dual
union
select 3 id, 25 age
from dual
union
select 4 id, 26 age from dual) B
on A.ID = B.ID在PL/SQL中上面写法怎么是错误的(在SQL Server中是可行的啊),我不想用下面的写法,而用left join,该怎么写?select A.ID, A.NAME, B.ID, B.AGE
from (select 1 id, 'AAA' name
from dual
union
select 2 id, 'BBB' name
from dual
union
select 3 id, 'CCC' name
from dual
union
select 1 id, 'DDD' name from dual) A,
(select 1 id, 23 age
from dual
union
select 2 id, 24 age
from dual
union
select 3 id, 25 age
from dual
union
select 4 id, 26 age from dual) B
where A.ID = B.ID(+)
from (select 1 id, 'AAA' name
from dual
union
select 2 id, 'BBB' name
from dual
union
select 3 id, 'CCC' name
from dual
union
select 1 id, 'DDD' name from dual) A
left join
(select 1 id, 23 age
from dual
union
select 2 id, 24 age
from dual
union
select 3 id, 25 age
from dual
union
select 4 id, 26 age from dual) B
on A.ID = B.ID在PL/SQL中上面写法怎么是错误的(在SQL Server中是可行的啊),我不想用下面的写法,而用left join,该怎么写?select A.ID, A.NAME, B.ID, B.AGE
from (select 1 id, 'AAA' name
from dual
union
select 2 id, 'BBB' name
from dual
union
select 3 id, 'CCC' name
from dual
union
select 1 id, 'DDD' name from dual) A,
(select 1 id, 23 age
from dual
union
select 2 id, 24 age
from dual
union
select 3 id, 25 age
from dual
union
select 4 id, 26 age from dual) B
where A.ID = B.ID(+)
SQL> select A.ID, A.NAME, B.ID, B.AGE
2 from (select 1 id, 'AAA' name
3 from dual
4 union
5 select 2 id, 'BBB' name
6 from dual
7 union
8 select 3 id, 'CCC' name
9 from dual
10 union
11 select 1 id, 'DDD' name from dual) A
12 left join
13 (select 1 id, 23 age
14 from dual
15 union
16 select 2 id, 24 age
17 from dual
18 union
19 select 3 id, 25 age
20 from dual
21 union
22 select 4 id, 26 age from dual) B
23 on A.ID = B.ID ; ID NAM ID AGE
---------- --- ---------- ----------
1 DDD 1 23
1 AAA 1 23
2 BBB 2 24
3 CCC 3 25这个语法没有什么错误呀?你要把空行去掉才行的;
2 from (select 1 id, 'AAA' name
3 from dual
4 union
5 select 2 id, 'BBB' name
6 from dual
7 union
8 select 3 id, 'CCC' name
9 from dual
10 union
11 select 1 id, 'DDD' name from dual) A,
12 (select 1 id, 23 age
13 from dual
14 union
15 select 2 id, 24 age
16 from dual
17 union
18 select 3 id, 25 age
19 from dual
20 union
21 select 4 id, 26 age from dual) B
22 where A.ID = B.ID(+); ID NAM ID AGE
---------- --- ---------- ----------
1 AAA 1 23
1 DDD 1 23
2 BBB 2 24
3 CCC 3 25第二句也没有什么错误的;这是我在sqlplus里面运行都通过的
SQL> select A.ID, A.NAME, B.ID, B.AGE
2 from (select 1 id, 'AAA' name
3 from dual
4 union
5 select 2 id, 'BBB' name
6 from dual
7 union
8 select 3 id, 'CCC' name
9 from dual
10 union
11 select 1 id, 'DDD' name from dual) A
12 left join
13 (select 1 id, 23 age
14 from dual
15 union
16 select 2 id, 24 age
17 from dual
18 union
19 select 3 id, 25 age
20 from dual
21 union
22 select 4 id, 26 age from dual) B
23 on A.ID = B.ID;
left join
*
ERROR 位于第 12 行:
ORA-00933: SQL 命令未正确结束
SQL>
2 from (select 1 id, 'AAA' name
3 from dual
4 union
5 select 2 id, 'BBB' name
6 from dual
7 union
8 select 3 id, 'CCC' name
9 from dual
10 union
11 select 1 id, 'DDD' name from dual) A
12 left join
13 (select 1 id, 23 age
14 from dual
15 union
16 select 2 id, 24 age
17 from dual
18 union
19 select 3 id, 25 age
20 from dual
21 union
22 select 4 id, 26 age from dual) B
23* on A.ID = B.ID
SQL> / ID NAM ID AGE
---------- --- ---------- ----------
1 AAA 1 23
1 DDD 1 23
2 BBB 2 24
3 CCC 3 25SQL>
我执行也没错
我的是8.1.7
from (select 1 id, 'AAA' name
from dual
union
select 2 id, 'BBB' name
from dual
union
select 3 id, 'CCC' name
from dual
union
select 1 id, 'DDD' name from dual) A
,
(select 1 id, 23 age
from dual
union
select 2 id, 24 age
from dual
union
select 3 id, 25 age
from dual
union
select 4 id, 26 age from dual) B
where A.ID = B.ID;