oracle数据库的SQL转换到postgresql,关于表连接的问题。
(好吧,其实要转换到富士通的Symfoware数据库,不过Symfoware是建立在postgresql上的。)
问题一:我在网上查到的例子:
ORACLE:
SELECT COUNT(DISTINCT(A.COL1)) AS RCOUNT FROM
SCHEMA.PREFIX_TABLE1 A,SCHEMA.PREFIX_TABLE2 B,SCHEMA.PREFIX_TABLE3 C,SCHEMA.PREFIX_TABLE4 D
WHERE 1 = 1
AND A.COL2 = B.COL2
AND A.COL3 = C.COL3(+)
AND A.COL4 = D.COL4(+)
AND A.COL5 > 0
AND A.COL6 = '1'
POSTGRESQL:
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)
left outer join schema.prefix_table3 c on (a.col3 = c.col3)
left outer join schema.prefix_table4 d on (a.col4 = d.col4)
where 1 = 1
and a.col5 > 0
and a.col6 = '1'
这里有一个问题?A.COL2 = B.COL2这一句,必须要写在inner join的on中吗?不可以直接写在where语句中吗?
问题二:
如下的一个oracle的SQL语句,转换为postgresql上是什么样子的呢?
ORACLE:
SELECT A.KEY1,A.KEY2,A.KEY3
FROM A,B,C,D,E
WHERE
A.KEY1 = B.KEY1(+) AND
A.KEY2 = B.KEY2(+) AND
B.KEY3 = C.KEY3(+) AND
A.KEY4 = D.KEY2(+) AND
A.KEY5 = E.KEY1
这里关于A表和B,D的左连接我知道了,但是与A表无关的B.KEY3 = C.KEY3(+)应该怎样转换呢?
请教各位了,多谢!