问卷答案的整理

有四张表，分别对不同类型的问题进行汇总，数据是一个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