Oracle存储过程迁移ODPS-03（专有云）：ODPS1.0支持exists语法

专有云目前还有不少ODPS1.0版本，主要是应对V3之前的平台版本。这个版本的ODPS不支持exists语法，如何换种写法实现。

-- ODPS1.0 如何实现 exists 语法 -- oracle select col1,col2,col3,col4 from (select t1.col1,t1.col2,t2.col3,t2.col4 from table1 t1 INNER JOIN table2 t2 ON t1.col1 = t2.col1 WHERE (t2.col3 between '14400000000' and '14499000000') and (NOT EXISTS (SELECT 1 FROM table3 t3 WHERE t1.col2 = t3.col2 AND GREATEST(t3.col5,t3.col6,t3.col7) <= sysdate() AND GREATEST(t3.col5,t3.col6,t3.col7) >= sysdate() - interval '1095' day))) foo group by col1 order by col1; -- odps select col1,col2,col3,col4 from (select t1.col1,t1.col2,t2.col3,t2.col4 from table1 t1 inner join table2 t2 ON t1.col1 = t2.col1 left outer join( SELECT col2 FROM table3 t3 WHERE GREATEST(t3.col5,t3.col6,t3.col7) <= getdate() AND GREATEST(t3.col5,t3.col6,t3.col7) >= getdate() - 1095) t3 ON t1.col2 = t3.col2 WHERE (t2.col3 >= '14400000000' and t2.col3 <='14499000000') and t3.col2 is null ) foo group by col1 order by col1; -- 暮角 15901445705 update at 20181224

exists写法在oracle上其实就是可用，可不用的写法。主要是使用exists语法的查询效率高于JOIN，所以，很多人在使用。