在结果集PostgreSQL中创建自动增量列
发布时间:2020-12-13 16:05:38 所属栏目:百科 来源:网络整理
导读:只有来自path的顺序是preserver时,此查询的结果才有意义.但是,在我的Left Inner Join中,结果是混乱的.我想我可以在结果集中创建一个新列,就像结果的索引一样,然后作为整个sql的最后一个子句添加一个ORDER BY idx. 这是我的查询而不试图保留最终的顺序: SELE
只有来自path的顺序是preserver时,此查询的结果才有意义.但是,在我的Left Inner Join中,结果是混乱的.我想我可以在结果集中创建一个新列,就像结果的索引一样,然后作为整个sql的最后一个子句添加一个ORDER BY idx.
这是我的查询而不试图保留最终的顺序: SELECT path.*,network_link.v0prt FROM (SELECT * // Need order preserved from this one FROM shortest_path_shooting_star( 'SELECT gid as id,source::integer,target::integer,distance::double precision as cost,x1,y1,x2,y2,rule,to_cost FROM network_link as net ORDER BY net.gid',9,1,false,false)) as path LEFT OUTER JOIN (SELECT DISTINCT gid,v0prt FROM network_link) as network_link ON (network_link.gid=path.edge_id); 任何见解都会很棒. 谢谢. SELECT path.*,network_link.v0prt FROM (SELECT incr(0) as idx,* FROM shortest_path_shooting_star( 'SELECT gid as id,v0prt FROM network_link) as network_link ON (network_link.gid=path.edge_id) ORDER BY idx; 解决方法
要保留shortest_path_shooting_star返回的顺序(如果不提供其他方法),可以使用窗口函数row_number来跟踪原始顺序,并按顺序查看其结果:
SELECT path.*,network_link.v0prt FROM (SELECT row_number() OVER() AS row_number,* FROM shortest_path_shooting_star( 'SELECT gid as id,v0prt FROM network_link) as network_link ON (network_link.gid=path.edge_id) ORDER BY path.row_number; 更新: 从PostgreSQL版本9.4及更新版本开始,更好的方法是使用 SELECT path.*,network_link.v0prt FROM shortest_path_shooting_star( 'SELECT gid as id,false) ) WITH ORDINALITY AS path LEFT OUTER JOIN (SELECT DISTINCT gid,v0prt FROM network_link) as network_link ON (network_link.gid=path.edge_id) ORDER BY path.ordinality; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |