hive sql a表为主表 A表为主表 B表为维度表 C 为结果表

Question

类似这种
A表为主表 B表为维度表 C 为结果表 我应该怎么写sql
本来是先转可以匹配的 再转匹配不上的，但匹配不上的机器转不成功

Answer 1

解决了。

with a as (
select '1' c1,'a' c2 union all
select '2' c1,'b' c2 union all
select '3' c1,'c' c2 union all
select '4' c1,'a,b' c2 union all
select '5' c1,'c,d' c2
),
b as (
select '1' c1,'a' c2 union all
select '2' c1,'b' c2 union all
select '3' c1,'c' c2 union all
select '4' c1,'d' c2
)
select
x.c1,
concat_ws(',',collect_list(y.c1)) c2
from (
select
c1,
c2,
d
from a
lateral view explode(split(c2,',')) t as d
) x
join b as y
on x.d=y.c2
group by x.c1

结果
+-------+------+--+
| x.c1 | c2 |
+-------+------+--+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 1,2 |
| 5 | 3,4 |
+-------+------+--+