请考虑以下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.预期的结果应该是a-la：
SELECT <magic_enumeration>,name from dreams WHERE type='nice' ORDER BY name; magic_enumeration | name -------------------+-------- 1 | Bird 2 | Donkey 3 | Monkey (3 rows)
有关如何按外观顺序枚举查询结果的任何想法？
解决方法
尝试使用row_number,这是一个窗口函数
SELECT row_number() OVER (ORDER BY name) sid,<-- magic enumeration!
name
FROM dreams
WHERE type='nice'
ORDER BY name;
