hiveSQL面试题12__多容器--转多行

0- 描述

描述：多容器–转多行
表名：t12
表字段及内容：

a        b      c
001     A/B     1/3/5
002     B/C/D   4/5

1- 问题一

描述：转多行
输出结果如下所示：

a        d       e
001     type_b    A
001     type_b    B
001     type_c    1
001     type_c    3
001     type_c    5
002     type_b    B
002     type_b    C
002     type_b    D
002     type_c    4
002     type_c    5

参考答案:

select 
  a,
  d,
  e
from 
(
  select
    a,
    "type_b" as d,
    str as e
  from t12
  lateral view explode(split(b,"/")) t as str
  union all 
  select
    a,
    "type_c" as d,
    str as e
  from t12
  lateral view explode(split(c,"/")) t as str
) tmp
order by a,d;