row_number()总是返回1，因为源表只有一行

Question

让我们考虑这个最小的例子：

SELECT row_number() over() as GID, 
unnest(myarray)     as letter
FROM 
(
SELECT string_to_array('a,b,b', ',') as myarray
) AS T

现在，由于表T只包含一个元素，即使返回了3行，row_number()函数也总是返回1。

所以我得到：

GID   letter
1     "a"
1     "b"
1     "b"

代替

GID   letter
1     "a"
2     "b"
3     "b"

如何修复？

Answer 1

将unnest子句放在string_to_array调用的右边，这样T就已经有多行了：

SELECT row_number() over() as GID, myarray as letter
FROM (
SELECT unnest(string_to_array('a,b,c', ',')) as myarray
) AS T

此外，我建议使用WITH ORDINALITY而不是row_number()来获得索引数组元素：

SELECT gid, letter
FROM UNNEST(string_to_array('a,b,c', ',')) WITH ORDINALITY AS T(gid, letter)

如果您想保持数组返回选择表达式，这也适用

SELECT gid, letter
FROM UNNEST(
(SELECT string_to_array('a,b,c', ',') as myarray)
) WITH ORDINALITY AS T(letter, gid)

或者使用横向查询：

SELECT gid, letter
FROM
(SELECT string_to_array('a,b,c', ',') as myarray) AS T,
UNNEST(T.myarray) WITH ORDINALITY AS U(letter, gid)

Answer 2

按照编辑前的示例，尝试以下查询：

SELECT 
row_number() OVER () AS gid, ST_AsText(geom)
FROM (SELECT 
st_makepixel(
ST_GeomFromText('POLYGON((2580000 1182000,2581000 1182000,2581000 1183000,2580000 1183000,2580000 1182000))',2056),200.0,50) AS geom) j
gid |                                         st_astext                                          
-----+--------------------------------------------------------------------------------------------
1 | POLYGON((2580000 1182000,2580200 1182000,2580200 1182200,2580000 1182200,2580000 1182000))
2 | POLYGON((2580200 1182000,2580400 1182000,2580400 1182200,2580200 1182200,2580200 1182000))
3 | POLYGON((2580400 1182000,2580600 1182000,2580600 1182200,2580400 1182200,2580400 1182000))
4 | POLYGON((2580600 1182000,2580800 1182000,2580800 1182200,2580600 1182200,2580600 1182000))
5 | POLYGON((2580800 1182000,2581000 1182000,2581000 1182200,2580800 1182200,2580800 1182000))
6 | POLYGON((2580000 1182200,2580200 1182200,2580200 1182400,2580000 1182400,2580000 1182200))
7 | POLYGON((2580200 1182200,2580400 1182200,2580400 1182400,2580200 1182400,2580200 1182200))
8 | POLYGON((2580400 1182200,2580600 1182200,2580600 1182400,2580400 1182400,2580400 1182200))
9 | POLYGON((2580600 1182200,2580800 1182200,2580800 1182400,2580600 1182400,2580600 1182200))
10 | POLYGON((2580800 1182200,2581000 1182200,2581000 1182400,2580800 1182400,2580800 1182200))
11 | POLYGON((2580000 1182400,2580200 1182400,2580200 1182600,2580000 1182600,2580000 1182400))
12 | POLYGON((2580200 1182400,2580400 1182400,2580400 1182600,2580200 1182600,2580200 1182400))
13 | POLYGON((2580400 1182400,2580600 1182400,2580600 1182600,2580400 1182600,2580400 1182400))
14 | POLYGON((2580600 1182400,2580800 1182400,2580800 1182600,2580600 1182600,2580600 1182400))
15 | POLYGON((2580800 1182400,2581000 1182400,2581000 1182600,2580800 1182600,2580800 1182400))
16 | POLYGON((2580000 1182600,2580200 1182600,2580200 1182800,2580000 1182800,2580000 1182600))
17 | POLYGON((2580200 1182600,2580400 1182600,2580400 1182800,2580200 1182800,2580200 1182600))
18 | POLYGON((2580400 1182600,2580600 1182600,2580600 1182800,2580400 1182800,2580400 1182600))
19 | POLYGON((2580600 1182600,2580800 1182600,2580800 1182800,2580600 1182800,2580600 1182600))
20 | POLYGON((2580800 1182600,2581000 1182600,2581000 1182800,2580800 1182800,2580800 1182600))
21 | POLYGON((2580000 1182800,2580200 1182800,2580200 1183000,2580000 1183000,2580000 1182800))
22 | POLYGON((2580200 1182800,2580400 1182800,2580400 1183000,2580200 1183000,2580200 1182800))
23 | POLYGON((2580400 1182800,2580600 1182800,2580600 1183000,2580400 1183000,2580400 1182800))
24 | POLYGON((2580600 1182800,2580800 1182800,2580800 1183000,2580600 1183000,2580600 1182800))
25 | POLYGON((2580800 1182800,2581000 1182800,2581000 1183000,2580800 1183000,2580800 1182800))
(25 Zeilen)