如何在视图中添加ROW_NUMBER（）？ [英] How to add ROW_NUMBER() in a view?

问题描述

在PostgreSQL 8.4中，我想从3个ID为ID的表中创建一个视图。因此，我想采用以下结构：

In PostgreSQL 8.4 I want to create a view from 3 tables with id. So I want to have this structure in my view:

num serial,
name_dispatcher character varying(250)
the_geom geometry

我可以选择 name_dispatcher 和 the_geom 从表中：

 CREATE VIEW lineView
      AS SELECT 'name' AS name_dispatcher, the_geom
      FROM line1
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line2
      UNION
      SELECT 'name' AS name_dispatcher, the_geom
      FROM line3

如何创建 num 视图中的列？

更新

我找到了解决方法：

ROW_NUMBER() OVER(ORDER BY lineView.voltage)

但是我不知道如何在 ALTER VIEW 中使用它。我如何将它放在那里？

But I don't know how to use it in ALTER VIEW. How do I put it in there?

推荐答案

您不能使用 ALTER VIEW 删除或添加列。我引用有关ALTER VIEW的手册：

You can't use ALTER VIEW to drop or add columns. I quote the manual on ALTER VIEW:

ALTER VIEW 更改视图的各种辅助属性。 （如果
要修改视图的定义查询，请使用创建或替换
视图。）

ALTER VIEW changes various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW.)

但是简单的创建或替换视图并不能解决问题。 手册中的另一句话：

But a simple CREATE OR REPLACE VIEW won't cut it. Another quote from the manual:

新查询必须生成与
现有视图查询生成的列相同的列

The new query must generate the same columns that were generated by the existing view query

因此 DROP 和 CREATE 视图：

DROP VIEW lineview;

CREATE VIEW lineview AS
SELECT *, row_number() OVER(ORDER BY ???) AS num
FROM (
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line1

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line2

   UNION
   SELECT 'name' AS name_dispatcher, the_geom
   FROM line3
   ) x

我使用子查询是因为我假设您想向所有行添加 row_number（）。在这方面，您的问题很模糊。

如果您只想按无特定顺序的唯一ID，请使用 row_number（）OVER（）。

I use a subquery because I assume you want to add row_number() to all rows. Your question is vague in that respect.
If you just want a unique id in no particular order, use row_number() OVER().

这篇关于如何在视图中添加ROW_NUMBER（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！