问题:两支篮球队进行了激烈的篮球比赛,比分交替上升。比赛结束后,你有一张两队得分分数的明细表,记录了球队team,球员号码number,球员姓名name, 得分分数score 以及得分时间score_time(string,秒级)。现在球队要对比赛中表现突出的球员做出嘉奖,所以请你用sql统计出 1)比赛中帮助各自球队反超比分的球员姓名以及对应时间。 2)连续三次(及以上)为球队得分的球员名单以及对应时间段内获得的分数。
时间: 2023-02-08 21:56:53 浏览: 207
fast alm for minimizing the sum of two convex functions.pdf
1)比赛中帮助各自球队反超比分的球员姓名以及对应时间
```
WITH cte AS (
SELECT
team,
number,
name,
score,
score_time,
SUM(score) OVER (PARTITION BY team ORDER BY score_time) AS team_score,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY score_time) AS row_num
FROM scores
)
SELECT
team,
name,
score_time
FROM cte
WHERE
row_num = 1
AND score = (SELECT MIN(team_score) FROM cte)
```
2)连续三次(及以上)为球队得分的球员名单以及对应时间段内获得的分数
```
WITH cte AS (
SELECT
team,
name,
score,
score_time,
ROW_NUMBER() OVER (PARTITION BY team, name ORDER BY score_time) AS row_num
FROM scores
)
SELECT
team,
name,
SUM(score) AS total_score,
MIN(score_time) as start_time,
MAX(score_time) as end_time
FROM cte
WHERE
row_num >= 3
AND row_num = (SELECT MIN(row_num) FROM cte)
GROUP BY team, name
HAVING COUNT(*) >= 3
```
注意:上述 SQL 语句假设表名为 scores, 如果表名不同,请替换。
阅读全文