有四张表,分别对不同类型的问题进行汇总,数据是一个tel一个答案,同一个tel有对不同问题的回答,类似于:
tel | a1 |
123 | A |
123 | B |
123 | C |
四张表的结构一样,要做的是将四张表同一个答案放在同一列,类似于:
tel | a0 | a1 | a2 |
124 | A | B | null |
125 | null | null | C |
126 | A | null | null |
上表只是一张表的内容,tel没有对应的答案就为null
1.首先实现最基础的一张表的内容,要先找出所有答案一共多少种,找出枚举值,利用collect_set函数,得到类似["A","B","C"]的数组,对于每个tel也要找到对应的所有答案的数组,将总答案数组和tel对应答案数组比较,看总答案数组的每一个值是否出现在tel对应答案数组中,如果出现则输出
总答案数组的那个值,没有出现则为null,使用array_contains函数判断包含,总答案数组的每一个值通过case when去实现
2.四张表的实现是一样的方法,因为区分表,因此join key为表名,取最大的a1数作为字段数,基本过程是:
每一行是join后对应的,每一行判断包含即可
select tel, t2.table_name,
case when array_contains(m_a1, n_a1[0]) then n_a1[0] end as a0,
case when array_contains(m_a1, n_a1[1]) then n_a1[1] end as a1,
case when array_contains(m_a1, n_a1[2]) then n_a1[2] end as a2,
case when array_contains(m_a1, n_a1[3]) then n_a1[3] end as a3,
case when array_contains(m_a1, n_a1[4]) then n_a1[4] end as a4,
case when array_contains(m_a1, n_a1[5]) then n_a1[5] end as a5,
case when array_contains(m_a1, n_a1[6]) then n_a1[6] end as a6,
case when array_contains(m_a1, n_a1[7]) then n_a1[7] end as a7,
case when array_contains(m_a1, n_a1[8]) then n_a1[8] end as a8,
case when array_contains(m_a1, n_a1[9]) then n_a1[9] end as a9,
case when array_contains(m_a1, n_a1[10]) then n_a1[10] end as a10,
case when array_contains(m_a1, n_a1[11]) then n_a1[11] end as a11,
case when array_contains(m_a1, n_a1[12]) then n_a1[12] end as a12,
case when array_contains(m_a1, n_a1[13]) then n_a1[13] end as a13,
case when array_contains(m_a1, n_a1[14]) then n_a1[14] end as a14,
case when array_contains(m_a1, n_a1[15]) then n_a1[15] end as a15,
case when array_contains(m_a1, n_a1[16]) then n_a1[16] end as a16,
case when array_contains(m_a1, n_a1[17]) then n_a1[17] end as a17,
case when array_contains(m_a1, n_a1[18]) then n_a1[18] end as a18,
case when array_contains(m_a1, n_a1[19]) then n_a1[19] end as a19,
case when array_contains(m_a1, n_a1[20]) then n_a1[20] end as a20,
case when array_contains(m_a1, n_a1[21]) then n_a1[21] end as a21,
case when array_contains(m_a1, n_a1[22]) then n_a1[22] end as a22
from
(
select tel, collect_set(a1) as m_a1, table_name
from
(
select tel, a1, 'u1' as table_name
from u1
union
select tel, a1, 'u2' as table_name
from u2
union
select tel, a1, 'u3' as table_name
from u3
union
select tel, a1, 'u4' as table_name
from u4
) t4
group by tel, table_name
) t2
inner join
(
select collect_set(a1) as n_a1, 'u1' as table_name
from u1
union
select collect_set(a1) as n_a1, 'u2' as table_name
from u2
union
select collect_set(a1) as n_a1, 'u3' as table_name
from u3
union
select collect_set(a1) as n_a1, 'u4' as table_name
from u4
) t3
on t2.table_name = t3.table_name