示例：有一个表t1，其中有列a1和a2。a1具有整数类型数据，a2具有数组结构。现在a2有一列c1。查询的语法应该是什么来查找a1的值对应于c1的某个值？
jv2fixgn1#
select distinct A1 from T1 lateral view explode(A2) a2 as swhere a2.s.C1 = {myvalue}
select distinct
A1
from T1 lateral view explode(A2) a2 as s
where a2.s.C1 = {myvalue}
演示
with T1 as ( select 1 as A1,array(named_struct('c1',7,'c2',1),named_struct('c1',7,'c2',8),named_struct('c1',5,'c2',2)) as A2 union all select 2 as A1,array(named_struct('c1',6,'c2',7),named_struct('c1',9,'c2',9)) as A2 union all select 3 as A1,array(named_struct('c1',7,'c2',7)) as A2 union all select 4 as A1,array(named_struct('c1',8,'c2',3),named_struct('c1',3,'c2',4)) as A2 ) select distinct A1from T1 lateral view explode(A2) a2 as swhere a2.s.C1 = 7;
with T1 as
(
select 1 as A1,array(named_struct('c1',7,'c2',1),named_struct('c1',7,'c2',8),named_struct('c1',5,'c2',2)) as A2
union all select 2 as A1,array(named_struct('c1',6,'c2',7),named_struct('c1',9,'c2',9)) as A2
union all select 3 as A1,array(named_struct('c1',7,'c2',7)) as A2
union all select 4 as A1,array(named_struct('c1',8,'c2',3),named_struct('c1',3,'c2',4)) as A2
)
where a2.s.C1 = 7
;
+----+| a1 |+----+| 1 || 3 |+----+
+----+
| a1 |
| 1 |
| 3 |
1条答案
按热度按时间jv2fixgn1#
演示