sqlite 基于使用上一行的条件更新表

表games：

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分的球员，或者在平局的情况下，在平局后第一个创造2分的球员（12-10，14-12，15-17）。
第一场比赛的数据是正确的。在B得分11-3之后，A输掉比赛，可以看作n_games += 1和games_lost += 1。然而，比赛的其余部分是错误的。如何根据上述标准更新n_games，games_won，games_lost？
所需输出：
| 匹配ID|玩家|对手|n_对策|赢的游戏|失去的游戏|n点|赢得的点数|失分|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 1|一个|B| 1| 0| 0| 1| 1| 0|
| 1|一个|B| 1| 0| 0| 2| 2| 0|
| 1|一个|B| 1| 0| 0| 3| 2| 1|
| 1|一个|B| 1| 0| 0| 4| 2| 2|
| 1|一个|B| 1| 0| 0| 5个|2| 3|
| 1|一个|B| 1| 0| 0|六|2| 4|
| 1|一个|B| 1| 0| 0|七个|2| 5个|
| 1|一个|B| 1| 0| 0|八|2|六|
| 1|一个|B| 1| 0| 0|九个|2|七个|
| 1|一个|B| 1| 0| 0|十个|2|八|
| 1|一个|B| 1| 0| 0|十一|2|九个|
| 1|一个|B| 1| 0| 0|十二岁|2|十个|
| 1|一个|B| 1| 0| 0|十三个|3|十个|
| 1|一个|B| 1| 0| 0|十四|3|十一|
| 1|一个|B| 2| 0| 1| 1| 0| 1|
| 1|一个|B| 2| 0| 1| 2| 0| 2|
| 1|一个|B| 2| 0| 1| 3| 0| 3|
| 1|一个|B| 2| 0| 1| 4| 1| 3|
| 1|一个|B| 2| 0| 1| 5个|2| 3|
| 1|一个|B| 2| 0| 1|六|3| 3|
| 1|一个|B| 2| 0| 1|七个|4| 3|
| 1|一个|B| 2| 0| 1|八|5个|3|
| 1|一个|B| 2| 0| 1|九个|5个|4|
| 1|一个|B| 2| 0| 1|十个|5个|5个|
| 1|一个|B| 2| 0| 1|十一|5个|六|
| 1|一个|B| 2| 0| 1|十二岁|5个|七个|
| 1|一个|B| 2| 0| 1|十三个|5个|八|
| 1|一个|B| 2| 0| 1|十四|5个|九个|
| 1|一个|B| 2| 0| 1|十五|六|九个|
| 1|一个|B| 2| 0| 1|十六|六|十个|
| 1|一个|B| 2| 0| 1|十七岁|六|十一|
| 1|一个|B| 3| 0| 2| 1| 0| 1|
| 1|一个|B| 3| 0| 2| 2| 0| 2|
| 1|一个|B| 3| 0| 2| 3| 0| 3|
| 1|一个|B| 3| 0| 2| 4| 0| 4|
| 1|一个|B| 3| 0| 2| 5个|1| 4|
| 1|一个|B| 3| 0| 2|六|1| 5个|
| 1|一个|B| 3| 0| 2|七个|1|六|
| 1|一个|B| 3| 0| 2|八|1|七个|
| 1|一个|B| 3| 0| 2|九个|1|八|
| 1|一个|B| 3| 0| 2|十个|1|九个|
| 1|一个|B| 3| 0| 2|十一|1|十个|
| 1|一个|B| 3| 0| 2|十二岁|1|十一|

如果行是按照得分的顺序插入的，我们可以使用ROWID作为比赛的时间顺序。
n_games、games_won和games_lost在每次n_points从1重新启动时递增。你不需要检查游戏持续了多少分。首先，需要更正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子句中。

sqlite 基于使用上一行的条件更新表

1条答案

相关问题

热门标签

最新问答