配置单元sql连接语句

tpgth1q7  于 2021-05-29  发布在  Hadoop
关注(0)|答案(1)|浏览(360)

我有个问题

with ex as (
select z.num number, car.id as id, car.make as make, car.model, 'vehicles' as type from mytable mt LATERAL VIEW EXPLODE(mt.vehicles) vehiclestbl as honda
where car.working = true
union all
select z.num number, plane.id as id, plane.make as make, plane.model, 'flights' as type from mytable mt LATERAL VIEW EXPLODE(mt.flights) flightstbl as wings
where plane.active = true
union all
select z.num number, train.id as id, train.make as make, train.model, 'departures' as type from mytable mt LATERAL VIEW EXPLODE(mt.departures) departurestbl as wheels
where wheels.active = true
)

select ex0.*, 1499 as cost, model as location
from ex ex0
where mycolumn = 15

最终结果是这样的

number    id      make     model    type    cost      location
323       abc     make1    model1   type1   1499      modelLoc1
329       xyz     make2    model2   type2   1499      modelLoc2
984       lks     make3    model3   type3   1499      modelLoc3

我正在尝试将列“number”与另一个外部表联接。当我添加

INNER JOIN myothertable mot on mot.id = ex_id;

我得到一个'无法识别输入附近'的错误

jjhzyzn0

jjhzyzn01#

在cte中,该列称为 number 所以我希望查询看起来像:

select ex0.*, 1499 as cost, model as location
from ex ex0 inner join
     myothertable mot
     on mot.id = ex0.number;
where mycolumn = 15;

相关问题