如何在PostgreSQL中为SELECT查询结果生成编号索引？

Question

我有一个2人数字棋盘游戏相关的数据库。我已经创建了一个查询，当给定用户id时，它将返回已玩过的游戏记录的列表。我希望结果伴随着一个带编号的索引，但我无法找到一种生成索引的方法。

我的数据库中有4个表:用户、游戏、布局和users_games。前3个表都有一个id列，它是主键，在引用它的表中用作外键。users_games表没有主键。

​

​

到目前为止，我的查询如下：

SELECT DISTINCT ON (games.id) row_number() over() AS num,
 (SELECT nickname FROM users INNER JOIN users_games ON users.id = users_games.user_id WHERE id != 'some_user_id' AND game_id = games.id ) AS opponent_name,
 (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') as opponent_score, 
 (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') as player_score,
 (SELECT
  CASE
   WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') THEN 'Won'
   WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') THEN 'Lost'
   ELSE 'Tie'
   END) as result,
 layouts.name AS layout, date(end_time) AS date
FROM
 users_games
INNER JOIN
 users ON users_games.user_id = users.id
INNER JOIN
 games ON users_games.game_id = games.id
INNER JOIN layouts
 ON games.layout_id = layouts.id 
 WHERE games.id IN (SELECT game_id FROM users_games WHERE user_id = 'some_user_id') 
 ORDER BY games.id asc; 

我觉得我写这篇文章的方式效率很低，很难读懂，但那是另一个问题。

我面临的问题是我无法获得编号索引。

以下是上述查询的结果：

 num | opponent_name | opponent_score | player_score | result | layout  |    date    
-----+---------------+----------------+--------------+--------+---------+------------
   1 | Medium AI     |             34 |            3 | Lost   | oldcity | 2021-08-31
   3 | Easy AI       |             21 |           31 | Won    | razeway | 2021-09-01
   5 | Easy AI       |             43 |           22 | Lost   | razeway | 2021-09-01
   7 | Easy AI       |             10 |           32 | Won    | razeway | 2021-09-01
   9 | Medium AI     |             10 |           32 | Won    | razeway | 2021-09-01
  11 | Medium AI     |             34 |           15 | Lost   | razeway | 2021-09-01
  13 | Medium AI     |             56 |           21 | Lost   | oldcity | 2021-09-01
  15 | Easy AI       |             22 |           22 | Tie    | razeway | 2021-09-03

如您所见，num列在每一列上都跳过了一个数字。这似乎是因为我的查询实际上生成了2行，每个游戏中的每个用户1行，我在查询中使用DISTINCT ON (games.id)解决了这一问题。

我还尝试使用count(*)生成num索引，或者使用group by games.id或group by game_id，但在每种情况下都会得到以下错误：

ERROR: subquery uses ungrouped column "games.id" from outer query

如何为查询结果中的每一行生成一个从1开始并递增1的数值索引？

Answer 1

去掉DISTINCT ON，然后让你的查询返回你想要的没有编号的结果。然后，将整个内容包装在SELECT row_number() over (), * from (...)_中。结尾的下划线很重要，Postgres需要命名FROM列表中的子查询。在本例中，我们将其命名为_。