SELECT基于DISTINCT列SQL的GROUPBY定义的值

Question

我有一个名为darts的表，如下所示：

CREATE TABLE "darts" (
"player"    TEXT,
"opponent"  TEXT,
"date"  TEXT,
"competition"   TEXT,
"round" TEXT,
"sets"  REAL,
"sets won"  REAL,
"sets lost" REAL,
"legs"  INTEGER,
"legs won"  INTEGER,
"legs lost" INTEGER,
"opponent score"    INTEGER,
"score_begin_of_turn"   INTEGER,
"score_after_first_dart"    INTEGER,
"score_after_second_dart"   TEXT,
"score_after_third_dart"    TEXT
);

数据库的每一行代表玩家的一个回合以及该回合中相应的分数。两个游戏者都以0"开始；集合"；以及0〃；腿；获胜，得分501。尽管数据表示匹配的时间序列；摘要"；每一场比赛。在howie的回答的帮助下，我写下了以下查询

player = 'michael van gerwen' 
opponent = 'gerwyn price'
query = f""" WITH DEDUPE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY player, opponent, date, competition, round   
ORDER BY player, opponent, date, competition, round) AS OCCURENCE
FROM darts)
SELECT *
WHERE OCCURENCE = 1 
AND player LIKE '{player}' 
AND opponent LIKE '{opponent}' """

此查询将生成两名玩家在每个日期、锦标赛和回合中的所有唯一匹配。输出如下：

player      opponent  ... OCCURENCE
0   Michael van Gerwen  Gerwyn Price  ...  1
1   Michael van Gerwen  Gerwyn Price  ...  1
2   Michael van Gerwen  Gerwyn Price  ...  1
3   Michael van Gerwen  Gerwyn Price  ...  1
4   Michael van Gerwen  Gerwyn Price  ...  1
5   Michael van Gerwen  Gerwyn Price  ...  1
etc

然而，我也想确定最终的分数。这可以通过使用列legs won和legs lost中的值来找到。我知道最后的分数等于

MAX(legs won) and MAX(legs lost)

然而，以上需要再次按player、opponent、date、competition和round分组。如何将其包含在查询中？请给我建议！感谢

期望输出：

player      opponent  ...  legs_player legs_opponent
0   Michael van Gerwen  Gerwyn Price  ...   7  5
1   Michael van Gerwen  Gerwyn Price  ...   4  3
2   Michael van Gerwen  Gerwyn Price  ...   2  7
3   Michael van Gerwen  Gerwyn Price  ...   5  6
4   Michael van Gerwen  Gerwyn Price  ...   4  0
5   Michael van Gerwen  Gerwyn Price  ...   0  4
etc

Answer 1

如果没有样本数据就无法确定，但似乎您想要聚合。

SELECT player, opponent, date, competition, round
, MAX(`legs won`) AS max_legs_won
, MAX(`legs lost`) AS max_legs_lost
FROM darts
WHERE player LIKE '{player}' 
AND opponent LIKE '{opponent}'
GROUP BY player, opponent, date, competition, round 
ORDER BY player, opponent, date, competition, round