1、LATERAL VIEW explode  → array转多行

select

    a.*

    ,b.ka_new

from(

    select

        order_id

        ,create_time

        ,model_info

        ,model_info_single

    from table_A

    LATERAL VIEW explode(model_info) str_table as model_info_single

    where

        date = ${date}

        and date_format(from_unixtime(create_time), 'yyyy-MM-dd') > '2021-04-01'

    ) a

    left join (

        select

            order_id

            ,ka_new

        from table_B

        where date = ${date}

    ) b

    on a.order_id = b.order_id