Блог

У меня есть база данных, связанная с цифровой настольной игрой на 2 игрока. Я создал запрос, который, получив идентификатор пользователя, вернет список записей сыгранных игр. Я хочу, чтобы результаты сопровождались пронумерованным индексом, но я не могу найти способ его создания.

В моей базе данных есть 4 таблицы: пользователи, игры, макеты и имена пользователей. Все первые 3 таблицы имеют столбец идентификатора, который является первичным ключом и используется в качестве внешнего ключа в таблицах, которые ссылаются на него. В таблице 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 для каждой строки в результатах моего запроса?

Избавьтесь от DISTINCT ON этого , а затем заставьте свой запрос возвращать нужные вам результаты без нумерации. Затем заверните все это в SELECT row_number() over (), * from (...)_ бумагу . Завершающее подчеркивание имеет значение, Postgres требует, чтобы подзапросы в FROM списке были названы. В данном случае мы называем это _ .