问题:两支篮球队进行了激烈的篮球比赛,比分交替上升。比赛结束后,你有一张两队得分分数的明细表,记录了球队team,球员号码number,球员姓名name, 得分分数score 以及得分时间score_time(string,秒级)。现在球队要对比赛中表现突出的球员做出嘉奖,所以请你用sql统计出 1)比赛中帮助各自球队反超比分的球员姓名以及对应时间。 2)连续三次(及以上)为球队得分的球员名单以及对应时间段内获得的分数。
时间: 2023-02-08 16:56:53 浏览: 65
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, 如果表名不同,请替换。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)