SQL 列转行方法

1. ClickHouse

select * from city;
结果:
┌─province──┬─arr_city──────────────────────────┬─arr_rank─┐
│ hubei     │ ['wuhan','xiangyang']             │ [1,2]    │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ [1,2,3]  │
│ beijing   │ []                                │ [10]     │
│ shanghai  │ []                                │ [20]     │
│ hongkong  │ []                                │ []       │
└───────────┴───────────────────────────────────┴──────────┘

-- 第一种情况：数组
1) 单列转行
select province,city from city array join arr_city as city;
结果:
┌─province──┬─city──────┐
│ hubei     │ wuhan     │
│ hubei     │ xiangyang │
│ guangdong │ guangzhou │
│ guangdong │ shenzhen  │
│ guangdong │ zhuhai    │
└───────────┴───────────┘

2）多列转行
select province,arr_city[i] as city,arr_rank[i] as rank
from(
  select province,
  arr_city,
  arr_rank,
  arrayEnumerate(arr_city) AS idx
  from city
)array join idx as i;
结果:
┌─province──┬─city──────┐─rank───┐
│ hubei     │ wuhan     │ 1      │
│ hubei     │ xiangyang │ 2      │
│ guangdong │ guangzhou │ 1      │
│ guangdong │ shenzhen  │ 2      │
│ guangdong │ zhuhai    │ 3      │
└───────────┴───────────┴────────┘

----------
-- 第二种情况：字符串
select * from city;
结果:
┌─province──┬─arr_city───────────┐
│ hubei     │ wuhan,xiangyang    │
└───────────┴────────────────────┘

select city from city array join splitByString(',',arr_city) as city;
结果:
┌─province──┬─city──────┐
│ hubei     │ wuhan     │
│ hubei     │ xiangyang │
└───────────┴───────────┘