mysql - 根据组内的最小日期更新表

Question

我有下表架构代表一些球员和他们效力的球队:

CREATE TABLE PLAYERS
(
  NAME VARCHAR(64) NOT NULL, 
  BIRTHDAY TIMESTAMP NOT NULL, 
  TEAM VARCHAR(64) NOT NULL, 
  CAPTAIN BOOLEAN
);

我的数据是这样的

"PLAYER1","1998-02-13 00:00:00","TEAM_A",NULL
"PLAYER2","1984-01-13 00:00:00","TEAM_A","1" 
"PLAYER3","1985-07-13 00:00:00","TEAM_A",NULL
"PLAYER4","1979-08-13 00:00:00","TEAM_B",NULL
"PLAYER5","1986-09-13 00:00:00","TEAM_B",NULL
"PLAYER6","1990-11-13 00:00:00","TEAM_B",NULL
"PLAYER7","1993-12-13 00:00:00","TEAM_C",NULL
"PLAYER8","1987-05-13 00:00:00","TEAM_C",NULL
"PLAYER9","1995-04-13 00:00:00","TEAM_C",NULL

现在我有一个要求，每个队只需要一个队长。 TEAM_A 已经有一个 (PLAYER2) 但 TEAM_B 和 TEAM_C 没有。所以我需要一个 SQl 脚本，它可以识别一支球队中年龄最大的球员，并为他们设置队长标志。谁能帮我解决这个问题。

Answer 1

试试这个

UPDATE players p1 
SET    captain = 1 
WHERE  birthday = (SELECT Min(birthday) 
                   FROM   players p2 
                   WHERE  p1.team = p2.team) 
AND CAPTAIN <> 1