我有两列城镇列表,我想把这些城镇拆分成行。
user 1 | [town1,town2,town3]
我想把它分成几行:
user 1 | town 1
user 1 | town 2
user 1 | town 3
我有两列城镇列表,我想把这些城镇拆分成行。
user 1 | [town1,town2,town3]
user 1 | town 1
user 1 | town 2
user 1 | town 3
user_name
和towns
,您可以像这样操作:select t1.user_name, t2.town
from the_table t1
cross join lateral unnest(string_to_array(t1.towns,',')) as t2 (town)
--Get some sample data
WITH test(username, town) as
(
select 'user 1', array['town1', 'town2', 'town3']
UNION
select 'user 2', array['town3', 'town2', 'town5']
)
--The following gives the results you are after.
SELECT username, unnest(town)
FROM test
我完全同意使用unnest函数,但我不理解cross join的原因。
楼主,你的“list”存储为何种数据类型非常重要。在这里,我假设它存储为ARRAY。如果它存储为字符串,则Rahul的答案就可以解决问题。