本文介绍了如何在PostgreSQL中将数组拆分为行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
运行此查询时:
SELECT id,selected_placements FROM app_data.content_cards我得到一个这样的表:
+----+-------------------------------+ | id | selected_placements | +----+-------------------------------+ | 90 | {162,108,156,80,163,155,NULL} | +----+-------------------------------+ | 91 | {} | +----+-------------------------------+我现在要做的是获取相同的信息,但是将数组分成几行,所以我得到这样的结果:
What I want to do now is get this same information but with the arrays splitted into rows so I get a result like this:
+----+---------------------+ | id | selected_placements | +----+---------------------+ | 90 | 162 | +----+---------------------+ | 90 | 108 | +----+---------------------+ | 90 | 156 | +----+---------------------+ | 90 | 80 | +----+---------------------+ | 90 | 163 | +----+---------------------+ | 90 | 155 | +----+---------------------+如您所见,我不想在"selected_placements"中获取具有空值的行.
As you can see I don't want to get rows with null value in "selected_placements".
我正在使用PostgreSQL 8.0.2.
I am using PostgreSQL 8.0.2.
非常感谢!
推荐答案我建议您升级Postgres版本.所有受支持的版本均支持 unnest():
I would suggest that you upgrade your version of Postgres. All supported versions support unnest():
SELECT x.* FROM (SELECT id, UNNEST(selected_placements) as selected_placement FROM app_data.content_cards ) x WHERE selected_placement IS NOT NULL;在早期版本中,您可以尝试一次将它们选出来.尽管已在9.5版中进行了测试,但仍可以正常工作:
In earlier versions, you can strive to pick them out one at a time. The following is tested and works, albeit in 9.5:
with content_cards as ( select 1 as id, array['a', 'b', 'c'] as selected_placements ) SELECT id, selected_placements[num] as selected_placement FROM (SELECT cc.*, generate_series(1, ccup.maxup) as num FROM content_cards cc CROSS JOIN (SELECT MAX(ARRAY_UPPER(cc.selected_placements, 1)) as maxup FROM content_cards cc ) ccup ) x WHERE selected_placements[num] IS NOT NULL;更多推荐
如何在PostgreSQL中将数组拆分为行
发布评论