SQL 如何在视图中添加 ROW_NUMBER()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14394002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add ROW_NUMBER() in a view?
提问by Kliver Max
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:
在 PostgreSQL 8.4 中，我想从 3 个带有 id 的表创建一个视图。所以我想在我看来有这个结构：
num serial,
name_dispatcher character varying(250)
the_geom geometry
I can select name_dispatcherand the_geomfrom tables:
我可以选择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
How to create the numcolumn in the view?
如何num在视图中创建列？
UPDATE
更新
I found a solution:
我找到了一个解决方案：
ROW_NUMBER() OVER(ORDER BY lineView.voltage)
But I don't know how to use it in ALTER VIEW. How do I put it in there?
但我不知道如何在ALTER VIEW. 我怎么把它放在那里？
回答by Erwin Brandstetter
You can't use ALTER VIEWto drop or add columns. I quote the manual on ALTER VIEW:
您不能用于ALTER VIEW删除或添加列。我引用了 ALTER VIEW 的手册：
ALTER VIEWchanges various auxiliary properties of a view. (If you want to modify the view's defining query, useCREATE OR REPLACE VIEW.)
ALTER VIEW更改视图的各种辅助属性。（如果要修改视图的定义查询，请使用CREATE OR REPLACE VIEW。）
But a simple CREATE OR REPLACE VIEWwon't cut it. Another quote from the manual:
但简单的CREATE OR REPLACE VIEW不会削减它。手册中的另一句话：
The new query must generate the same columns that were generated by the existing view query
新查询必须生成与现有视图查询生成的列相同的列
So DROPand CREATEthe view:
所以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
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()到所有行。你的问题在这方面是模糊的。
如果您只想要一个没有特定顺序的唯一 ID，请使用row_number() OVER().
