sql AB球队得分流水表，得到连续三次得分的队员名字和每次赶超对手的球员名字

封面图 • 2025-09-22 • SQL Server

要解决这个问题，我们需要理解两个关键点：

1. **连续三次得分的队员名字**：即某个球员在比赛中连续三次得分。
2. **每次赶超对手的球员名字**：即某个球员在某一时刻的总分超过对手。

假设你有一个名为 `score_log` 的表，结构如下（根据你的描述进行推测）：

```sql
CREATE TABLE score_log (
    id INT PRIMARY KEY,
    player_name VARCHAR(50),
    score INT,
    game_time TIMESTAMP,
    team VARCHAR(10) -- 例如 'A' 或 'B'
);
```

### 第一步：找出连续三次得分的队员

为了找出一个球员连续三次得分的情况，我们可以使用窗口函数来标记连续的记录。这里我们假设“连续”是指在同一场比赛中，且按时间顺序排列。

```sql
WITH ranked_scores AS (
    SELECT 
        player_name,
        game_time,
        ROW_NUMBER() OVER (PARTITION BY player_name ORDER BY game_time) AS rn,
        ROW_NUMBER() OVER (PARTITION BY player_name, team ORDER BY game_time) AS rn_team
    FROM score_log
),
consecutive_three AS (
    SELECT 
        player_name,
        COUNT(*) AS cnt
    FROM ranked_scores
    GROUP BY player_name, rn - rn_team
    HAVING COUNT(*) >= 3
)
SELECT DISTINCT player_name
FROM consecutive_three;
```

> 注意：这个查询可能需要根据实际数据调整，比如是否是同一场比赛、是否考虑团队等。

---

### 第二步：找出每次赶超对手的球员名字

假设我们有两个队伍 A 和 B，我们想找出哪些球员在某次得分后使得本队的总分超过对方。

首先，我们需要计算每个队伍的累计得分：

```sql
WITH team_score AS (
    SELECT 
        game_time,
        team,
        SUM(score) OVER (ORDER BY game_time) AS cumulative_score
    FROM score_log
),
team_a AS (
    SELECT 
        game_time,
        cumulative_score AS a_score
    FROM team_score
    WHERE team = 'A'
),
team_b AS (
    SELECT 
        game_time,
        cumulative_score AS b_score
    FROM team_score
    WHERE team = 'B'
)
SELECT 
    a.game_time,
    a.a_score,
    b.b_score,
    CASE WHEN a.a_score > b.b_score THEN 'A team leads' ELSE 'B team leads' END AS lead_status
FROM team_a a
JOIN team_b b ON a.game_time >= b.game_time
WHERE a.a_score > b.b_score
ORDER BY a.game_time;
```

然后，我们可以结合原始 `score_log` 表找到哪个球员在该时刻得分导致了领先：

```sql
WITH team_score AS (
    SELECT 
        game_time,
        team,
        SUM(score) OVER (PARTITION BY team ORDER BY game_time) AS cumulative_score
    FROM score_log
),
lead_changes AS (
    SELECT 
        t1.game_time,
        t1.team AS leading_team,
        t1.cumulative_score AS leading_score,
        t2.team AS trailing_team,
        t2.cumulative_score AS trailing_score
    FROM team_score t1
    JOIN team_score t2
        ON t1.game_time = t2.game_time
        AND t1.team <> t2.team
    WHERE t1.cumulative_score > t2.cumulative_score
)
SELECT 
    s.player_name,
    lc.game_time
FROM lead_changes lc
JOIN score_log s
    ON lc.game_time = s.game_time
    AND lc.leading_team = s.team
    AND s.score = (SELECT MAX(score) FROM score_log WHERE game_time = lc.game_time AND team = lc.leading_team);
```

---

### 总结

- 要找出**连续三次得分的球员**，可以使用窗口函数和分组。
- 要找出**每次赶超对手的球员**，需要比较两队的累计得分，并找到在该时刻得分的球员。

如果你有更具体的表结构或业务逻辑，请提供更多信息，我可以进一步优化查询。

sql AB球队得分流水表，得到连续三次得分的队员名字 和每次赶超对手的球员名字

sql AB球队得分流水表，得到连续三次得分的队员名字和每次赶超对手的球员名字