SQL 错误 10249 具有多个子查询的 Hive

Question

我试图在 where 子句中运行多个子查询，但出现以下错误。是否意味着 Hive 不支持它？如果不是，是否有不同的方式来编写下面的查询？

如果我这样写代码:

SELECT * 
FROM ide_test.flights 
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights
WHERE year >1
);

我会得到

[Error 10249]: Line 6:12 Unsupported SubQuery Expression 'carrier_code': SubQuery cannot use the table alias: flights; this is also an alias in the Outer Query and SubQuery contains a unqualified column reference

如果我这样写，在子查询或外部查询表的名称之外添加一个“s”:

SELECT * 
FROM ide_test.flights 
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights s
WHERE year >1
);

or

SELECT * 
FROM ide_test.flights s
WHERE carrier_code IN
(
SELECT carrier_code 
FROM ide_test.flights
WHERE year >1
);

然后就成功了

Answer 1

你可以尝试使用相关子查询

  SELECT a.* 
  FROM ide_test.flights a
  where exists (SELECT 1
  FROM ide_test.flights a1
  WHERE a1.carrier_code=a.carrier_code
  and  carrier_code <>1
                )