专有云目前还有不少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,所以,很多人在使用。