英文:
Error while converting Presto SQL to Hive SQL containing subquery
问题
我试图将以下Presto SQL转换为Hive SQL。在Hive中，WHERE子查询不起作用，并且引发错误。
select distinct id
from account
where load_date = (select max(load_date) from account)
and account_status='current'
and account_type='crnt'
请指导我如何修复这个问题，使其在Hive中工作。
英文:
I am trying to convert the following Presto SQL to Hive SQL. The sub query in WHERE clause isn't working in Hive and is throwing an error.
select distinct id
from account
where load_date = (select max(load_date) from account)
and account_status='current'
and account_type='crnt'
Please advise how I can fix this and make it work in Hive.
答案1
得分: 2
Hive不支持在WHERE子句中使用子查询。可以尝试以下两种方式：
第一种方式：
SELECT DISTINCT a.id
FROM account a
JOIN (
SELECT max(load_date) AS max_load_date
FROM account
) b
ON a.load_date = b.max_load_date
WHERE a.account_status='current'
AND a.account_type='crnt';
第二种方式：
WITH max_load_date AS (
SELECT max(load_date) AS max_date
FROM account
)
SELECT DISTINCT a.id
FROM account a
JOIN max_load_date m
ON a.load_date = m.max_date
WHERE a.account_status='current'
AND a.account_type='crnt';
英文:
Hive doesn't support subqueries in the WHERE clause. Try this
SELECT DISTINCT a.id
FROM account a
JOIN (
SELECT max(load_date) AS max_load_date
FROM account
) b
ON a.load_date = b.max_load_date
WHERE a.account_status='current'
AND a.account_type='crnt';
or this
WITH max_load_date AS (
SELECT max(load_date) AS max_date
FROM account
)
SELECT DISTINCT a.id
FROM account a
JOIN max_load_date m
ON a.load_date = m.max_date
WHERE a.account_status='current'
AND a.account_type='crnt';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论