请考虑以下SQL查询和响应:
CREATE TEMPORARY TABLE dreams (name text, type text);
INSERT INTO dreams VALUES ('Monkey', 'nice');
INSERT INTO dreams VALUES ('Snake', 'Not nice');
INSERT INTO dreams VALUES ('Donkey', 'nice');
INSERT INTO dreams VALUES ('Bird', 'nice');
SELECT name from dreams WHERE type='nice' ORDER BY name;
name
--------
Bird
Donkey
Monkey
(3 rows)
我希望按照出现顺序枚举结果,而不考虑任何现有的id,以便于操作。预期的结果应该是类似这样的:
SELECT <magic_enumeration>, name from dreams WHERE type='nice' ORDER BY name;
magic_enumeration | name
-------------------+--------
1 | Bird
2 | Donkey
3 | Monkey
(3 rows)
有什么办法可以按照出现的顺序枚举查询结果吗?
over (order by name)
来获得正确的排序编号。 - a_horse_with_no_nameSELECT row_number OVER (ORDER BY name) magic_enumeration, name...
返回ERROR: 语法错误,附近有“OVER”
。 - Adam Matan