根据使用前一行SQLITE3的条件更新表

我有下表

CREATE TABLE "games" (
    "match_id"    INTEGER,
    "player"    TEXT,
    "opponent"  TEXT,
    "n_games"    INTEGER,
    "games_won" INTEGER,
    "games_lost"  INTEGER,
    "n_points"  INTEGER,
    "points_won"  INTEGER,
    "points_lost"  INTEGER
);

一些示例数据如下所示：

INSERT INTO games (`match_id`, `player`, `opponent`, `n_games`, `games_won`, `games_lost`, `n_points`, `points_won`, `points_lost`) VALUES
  ('1', 'A', 'B', '1', '0', '0', '1', '1', '0'),
  ('1', 'A', 'B', '1', '0', '0', '2', '2', '0'),
  ('1', 'A', 'B', '1', '0', '0', '3', '2', '1'),
  ('1', 'A', 'B', '1', '0', '0', '4', '2', '2'),
  ('1', 'A', 'B', '1', '0', '0', '5', '2', '3'),
  ('1', 'A', 'B', '1', '0', '0', '6', '2', '4'),
  ('1', 'A', 'B', '1', '0', '0', '7', '2', '5'),
  ('1', 'A', 'B', '1', '0', '0', '8', '2', '6'),
  ('1', 'A', 'B', '1', '0', '0', '9', '2', '7'),
  ('1', 'A', 'B', '1', '0', '0', '10', '2', '8'),
  ('1', 'A', 'B', '1', '0', '0', '11', '2', '9'),
  ('1', 'A', 'B', '1', '0', '0', '12', '2', '10'),
  ('1', 'A', 'B', '1', '0', '0', '13', '3', '10'),
  ('1', 'A', 'B', '1', '0', '0', '14', '3', '11'),
  ('1', 'A', 'B', '2', '0', '1', '1', '0', '1'),
  ('1', 'A', 'B', '2', '0', '1', '2', '0', '2'),
  ('1', 'A', 'B', '2', '0', '1', '3', '0', '3'),
  ('1', 'A', 'B', '2', '0', '1', '4', '1', '3'),
  ('1', 'A', 'B', '2', '0', '1', '5', '2', '3'),
  ('1', 'A', 'B', '2', '0', '1', '6', '3', '3'),
  ('1', 'A', 'B', '2', '0', '1', '7', '4', '3'),
  ('1', 'A', 'B', '2', '0', '1', '8', '5', '3'),
  ('1', 'A', 'B', '2', '0', '1', '9', '5', '4'),
  ('1', 'A', 'B', '2', '0', '1', '10', '5', '5'),
  ('1', 'A', 'B', '2', '0', '1', '11', '5', '6'),
  ('1', 'A', 'B', '2', '0', '1', '12', '5', '7'),
  ('1', 'A', 'B', '2', '0', '1', '13', '5', '8'),
  ('1', 'A', 'B', '2', '0', '1', '14', '5', '9'),
  ('1', 'A', 'B', '2', '0', '1', '15', '6', '9'),
  ('1', 'A', 'B', '2', '0', '1', '16', '6', '10'),
  ('1', 'A', 'B', '2', '0', '1', '17', '6', '11'),
  ('1', 'A', 'B', '2', '0', '1', '1', '0', '1'),
  ('1', 'A', 'B', '2', '0', '1', '2', '0', '2'),
  ('1', 'A', 'B', '2', '0', '1', '3', '0', '3'),
  ('1', 'A', 'B', '2', '0', '1', '4', '0', '4'),
  ('1', 'A', 'B', '2', '0', '1', '5', '1', '4'),
  ('1', 'A', 'B', '2', '0', '1', '6', '1', '5'),
  ('1', 'A', 'B', '2', '0', '1', '7', '1', '6'),
  ('1', 'A', 'B', '2', '0', '1', '8', '1', '7'),
  ('1', 'A', 'B', '2', '0', '1', '9', '1', '8'),
  ('1', 'A', 'B', '2', '0', '1', '10', '1', '9'),
  ('1', 'A', 'B', '2', '0', '1', '11', '1', '10'),
  ('1', 'A', 'B', '2', '0', '2', '12', '1', '11');

由于刮擦时出现错误，我需要编辑上述数据。
一场比赛的赢家是第一个得到11分的球员，或者在10分的情况下，平局后第一个创造2分差的球员（12-10，14-12，15-17）
第一局数据正确，在 B 得11-3后，A 输了这局，可以看作是 *n_游戏 * += 1和 games_lost += 1，但是剩下的比赛是错误的。
如何根据上述标准更新 *n_游戏 *、games_won、games_lost。
预期输出：

('1', 'A', 'B', '1', '0', '0', '1', '1', '0'),
  ('1', 'A', 'B', '1', '0', '0', '2', '2', '0'),
  ('1', 'A', 'B', '1', '0', '0', '3', '2', '1'),
  ('1', 'A', 'B', '1', '0', '0', '4', '2', '2'),
  ('1', 'A', 'B', '1', '0', '0', '5', '2', '3'),
  ('1', 'A', 'B', '1', '0', '0', '6', '2', '4'),
  ('1', 'A', 'B', '1', '0', '0', '7', '2', '5'),
  ('1', 'A', 'B', '1', '0', '0', '8', '2', '6'),
  ('1', 'A', 'B', '1', '0', '0', '9', '2', '7'),
  ('1', 'A', 'B', '1', '0', '0', '10', '2', '8'),
  ('1', 'A', 'B', '1', '0', '0', '11', '2', '9'),
  ('1', 'A', 'B', '1', '0', '0', '12', '2', '10'),
  ('1', 'A', 'B', '1', '0', '0', '13', '3', '10'),
  ('1', 'A', 'B', '1', '0', '0', '14', '3', '11'),
  ('1', 'A', 'B', '2', '0', '1', '1', '0', '1'),
  ('1', 'A', 'B', '2', '0', '1', '2', '0', '2'),
  ('1', 'A', 'B', '2', '0', '1', '3', '0', '3'),
  ('1', 'A', 'B', '2', '0', '1', '4', '1', '3'),
  ('1', 'A', 'B', '2', '0', '1', '5', '2', '3'),
  ('1', 'A', 'B', '2', '0', '1', '6', '3', '3'),
  ('1', 'A', 'B', '2', '0', '1', '7', '4', '3'),
  ('1', 'A', 'B', '2', '0', '1', '8', '5', '3'),
  ('1', 'A', 'B', '2', '0', '1', '9', '5', '4'),
  ('1', 'A', 'B', '2', '0', '1', '10', '5', '5'),
  ('1', 'A', 'B', '2', '0', '1', '11', '5', '6'),
  ('1', 'A', 'B', '2', '0', '1', '12', '5', '7'),
  ('1', 'A', 'B', '2', '0', '1', '13', '5', '8'),
  ('1', 'A', 'B', '2', '0', '1', '14', '5', '9'),
  ('1', 'A', 'B', '2', '0', '1', '15', '6', '9'),
  ('1', 'A', 'B', '2', '0', '1', '16', '6', '10'),
  ('1', 'A', 'B', '2', '0', '1', '17', '6', '11'),
  ('1', 'A', 'B', '3', '0', '2', '1', '0', '1'),
  ('1', 'A', 'B', '3', '0', '2', '2', '0', '2'),
  ('1', 'A', 'B', '3', '0', '2', '3', '0', '3'),
  ('1', 'A', 'B', '3', '0', '2', '4', '0', '4'),
  ('1', 'A', 'B', '3', '0', '2', '5', '1', '4'),
  ('1', 'A', 'B', '3', '0', '2', '6', '1', '5'),
  ('1', 'A', 'B', '3', '0', '2', '7', '1', '6'),
  ('1', 'A', 'B', '3', '0', '2', '8', '1', '7'),
  ('1', 'A', 'B', '3', '0', '2', '9', '1', '8'),
  ('1', 'A', 'B', '3', '0', '2', '10', '1', '9'),
  ('1', 'A', 'B', '3', '0', '2', '11', '1', '10'),
  ('1', 'A', 'B', '3', '0', '2', '12', '1', '11');

如果行是按照得分的顺序插入的，我们可以使用ROWID作为比赛的时间顺序。
每次n_points从1重新开始时，n_games、games_won和games_lost都会递增。您不需要检查游戏持续了多少分。首先，您需要更正n_games列：

UPDATE games
SET n_games = 
    (SELECT count(*) FROM games g 
     WHERE g.match_id = games.match_id AND g.ROWID <= games.ROWID AND g.n_points=1 );

然后，您可以按match_id, n_games分组，以获得每个游戏的最终得分，并使用它来更新games_won和games_lost：

WITH final_scores AS (
    SELECT match_id, n_games, max(n_points) as last_point, points_won, points_lost
    FROM games g
    GROUP BY match_id, n_games
)
UPDATE games
SET games_won = (SELECT count(n_games) FROM final_scores fs WHERE fs.match_id = games.match_id AND fs.n_games < games.n_games AND fs.points_won > fs.points_lost),
games_lost = (SELECT count(n_games) FROM final_scores fs WHERE fs.match_id = games.match_id AND fs.n_games < games.n_games AND fs.points_won < fs.points_lost);

Here is a Fiddle with your data
注意：Sqlite将从取max（n_points）的同一行取points_won和points_lost，因为它们不在GROUP BY子句中。

根据使用前一行SQLITE3的条件更新表

1条答案

相关问题

热门标签

最新问答