mysql查询多列distinct加上一个辅助列条件

想象一下，一张跟踪游戏比赛的平板，每场比赛有三名参与者：一名攻击者、一名防守队员和一名下注于玩家1和玩家2之间战斗结果的下注者。这张表包括每场比赛的选手和投注者的姓名，以及比赛日期、每位选手的得分、比赛地点和裁判的姓名。我在下面为一些示例数据提供了createsql。

DROP TABLE IF EXISTS `game`;

CREATE TABLE `game` (
  `game_date` text,
  `player_1` text,
  `player_2` text,
  `bettor` text,
  `p1_score` double DEFAULT NULL,
  `p2_score` double DEFAULT NULL,
  `result` double DEFAULT NULL,
  `venue` text,
  `referee` text
) 

INSERT INTO `game` VALUES ('2020-04-05','Bob','Kelly','Kevin',100,78,0.2,'TS1','Richard'),('2020-03-06','Jim','Bob','Dave',100,97,1.2,'TS2','Mike'),('2020-02-05','Jim','Bob','Kevin',100,86,0.9,'TS2','Mike'),('2020-01-06','Kelly','Bob','Jim',100,92,1.3,'TS2','Richard'),('2019-12-07','Kelly','Bob','Jim',100,98,1.7,'TS1','Mike'),('2019-11-07','Kelly','Bob','Kevin',78,100,2.1,'TS2','Mike'),('2019-10-08','Kelly','Bob','Kevin',97,100,1.5,'TS1','Mike'),('2019-09-08','Kelly','Jim','Dave',86,100,2.4,'TS1','Richard'),('2019-08-09','Kelly','Jim','Dave',92,100,2.8,'TS2','Mike'),('2019-07-10','Kelly','Jim','Dave',98,100,2.2,'TS2','Mike'),('2019-06-10','Kelly','Jim','Dave',100,78,1.9,'TS2','Richard'),('2019-05-11','Sarah','Jim','Kevin',100,97,2.1,'TS1','Mike'),('2019-04-11','Sarah','Jim','Kevin',100,86,2.1,'TS2','Mike'),('2019-03-12','Sarah','Jim','Kevin',100,92,2.8,'TS1','Mike'),('2019-02-10','Sarah','Jim','Kevin',100,98,1.8,'TS1','Richard');

我需要一个查询，返回匹配参与者的每个唯一集合的匹配信息。。。但只适用于三个参赛者一起参加的第一场比赛，即三个参赛者参加的比赛中最早的比赛日期。
例如，一个游戏，鲍勃是玩家1，凯利是玩家2，凯文是投注者将构成一个独特的三人组。在数据中，这三人组只有一个这样的配对，因此查询将为这一个匹配返回一行。
在sarah作为玩家1，jim作为玩家2，kevin作为投注者的情况下，有四场比赛是三人组的，因此查询只返回最早的比赛的信息，即2019年2月10日的比赛。
请注意，在示例数据中，有两个匹配项与三人组“kelly”、“bob”、“jim”匹配。还有另外两个匹配的三人组“凯利”，“吉姆”，“鲍勃”。这是不一样的，因为鲍勃和吉姆交换的地方有球员2和投注。因此，查询将为它们中的每一个返回一行，即分别日期为'12/072019'和'08/09/2019'的匹配。
使用distinct，我可以返回所有唯一玩家分组的列表。

SELECT DISTINCT player_1, player_2, bettor FROM games;

使用groupby，我可以返回该组参与的所有比赛的所有游戏信息。

SELECT * FROM games GROUP BY player_1, player_2, bettor;

但我不知道如何返回所有的游戏信息，但只有最早的游戏，所有三个参与者一起玩，在游戏中的不同角色。
我曾经尝试过使用min（）对游戏日期进行子查询，但这是一个失败的结果。我怀疑可能有一个内部连接解决方案，但我还没有找到它。
我很感激你能提供的任何指导。

一种规范方法使用子查询的连接，该子查询为每个三人组标识最早的游戏：

SELECT g1.*
FROM games g1
INNER JOIN
(
    SELECT player_1_name, player_2_name, player_3_name,
           MIN(game_date) AS min_game_date
    FROM games
    GROUP BY player_1_name, player_2_name, player_3_name
) g2
    ON g2.player_1_name = g1.player_1_name AND
       g2.player_2_name = g1.player_2_name AND
       g2.player_3_name = g1.player_3_name AND
       g2.min_game_date = g1.game_date;

如果您运行的是mysql 8+，那么 ROW_NUMBER 分析函数提供了另一个选项：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY player_1_name, player_2_name,
                                              player_3_name
                                 ORDER BY game_date) rn
    FROM games
)

SELECT *
FROM cte
WHERE rn = 1;

mysql查询多列distinct加上一个辅助列条件

1条答案

相关问题

热门标签

最新问答