如果数据中包含＃并将其插入该表中的另一个表，如何分隔行的数据？

作者: 关于贤的记忆
发布时间: 2025-04-06 04:48:21 (3天前)
转自：

3 条回复

0#
回复此人
坚挺的阿袁 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 您可以使用 <code> ROW_NUMBER() </code> </p> <pre> <code> select ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY sector) as id, project_id, sector from (select id as project_id, unnest(string_to_array(sector, '#')) as sector from project) as t; </code> </pre> </DIV>

编辑
1#
回复此人
一生流水 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 你可以结合使用 <code> string_to_array </code> 和 <code> unnest </code> </p> <pre> <code> select id, unnest(string_to_array(sector, '#')) as sector, project from project; </code> </pre> <P> 此查询的输出当然可以连接到其他表。为了便于阅读，我更喜欢将其放入公用表表达式中。 </p> <pre> <code> with normalized_projects as ( select id, unnest(string_to_array(sector, '#')) as sector, project from project ) select p.id as project_Id, row_number() over (partition by p.id order by sector) as new_sector_id, p.sector, p.project, s.sector_id from normalized_projects p join sector s on s.sector_name = p.sector; </code> </pre> <P> 您可能还需要考虑将该规范化结构视为一个视图，这样您就不必在整个地方重复该查询 </p> <P> SQLFiddle示例： <a href="http://sqlfiddle.com/#!15/1fc14/3" rel="nofollow"> http://sqlfiddle.com/#!15/1fc14/3 </A> </p> </DIV>

编辑

登录后才能参与评论