用 R 解决 SQL 经典 50 题
楚新元 / 2025-06-26
- 更新 2025-06-30
-
因为 RSQLite 功能比 duckdb 弱，所以弃用；score 表是原始数据，不做任何修改，后续计算过程中考虑到因没有选修所有的课程导致的缺失值问题，提前生成了
score_longer和score_wider。
本文是张敬信老师《SQL 经典 50 题 – tidyverse 版》学习笔记，与张老师课件代码略有不同，供读者参考。
创建表
- 加载相关 R 包
library(duckdb)
library(dplyr)
library(tidyr)
library(lubridate)
use('tibble', 'tribble')
use('stringr', 'str_detect')
- 创建学生基础信息表
student = tribble(
~学号, ~姓名, ~生日, ~性别,
'01', '赵雷', '1990-01-01', '男',
'02', '钱电', '1990-12-21', '男',
'03', '孙风', '1990-05-20', '男',
'04', '李云', '1990-08-06', '男',
'05', '周梅', '1991-12-01', '女',
'06', '吴兰', '1992-03-01', '女',
'07', '郑竹', '1989-07-01', '女',
'08', '王菊', '1990-01-20', '女'
)
print(student)
#> # A tibble: 8 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
#> 5 05 周梅 1991-12-01 女
#> 6 06 吴兰 1992-03-01 女
#> 7 07 郑竹 1989-07-01 女
#> 8 08 王菊 1990-01-20 女
- 创建课程任课教师表
course = tribble(
~课程编号, ~课程名称, ~教师编号,
'01', '语文', '02',
'02', '数学', '01',
'03', '英语', '03'
)
print(course)
#> # A tibble: 3 × 3
#> 课程编号 课程名称 教师编号
#> <chr> <chr> <chr>
#> 1 01 语文 02
#> 2 02 数学 01
#> 3 03 英语 03
- 创建任课教师信息表
teacher = tribble(
~教师编号, ~教师姓名,
'01', '张三',
'02', '李四',
'03', '王五'
)
print(teacher)
#> # A tibble: 3 × 2
#> 教师编号 教师姓名
#> <chr> <chr>
#> 1 01 张三
#> 2 02 李四
#> 3 03 王五
- 创建学生成绩表
score = tribble(
~学号, ~课程编号, ~成绩,
'01', '01', 80,
'01', '02', 90,
'01', '03', 99,
'02', '01', 70,
'02', '02', 60,
'02', '03', 80,
'03', '01', 80,
'03', '02', 80,
'03', '03', 80,
'04', '01', 50,
'04', '02', 30,
'04', '03', 20,
'05', '01', 76,
'05', '02', 87,
'06', '01', 31,
'06', '03', 34,
'07', '02', 89,
'07', '03', 98
)
print(score)
#> # A tibble: 18 × 3
#> 学号 课程编号 成绩
#> <chr> <chr> <dbl>
#> 1 01 01 80
#> 2 01 02 90
#> 3 01 03 99
#> 4 02 01 70
#> 5 02 02 60
#> 6 02 03 80
#> 7 03 01 80
#> 8 03 02 80
#> 9 03 03 80
#> 10 04 01 50
#> 11 04 02 30
#> 12 04 03 20
#> 13 05 01 76
#> 14 05 02 87
#> 15 06 01 31
#> 16 06 03 34
#> 17 07 02 89
#> 18 07 03 98
保存数据表
base = dbConnect(duckdb(), './data/base.duckdb')
dbWriteTable(base, 'student', student, overwrite = TRUE)
dbWriteTable(base, 'course', course, overwrite = TRUE)
dbWriteTable(base, 'teacher', teacher, overwrite = TRUE)
dbWriteTable(base, 'score', score, overwrite = TRUE)
dbDisconnect(base)
加载数据表
base = dbConnect(duckdb(), './data/base.duckdb')
student = collect(tbl(base, 'student'))
course = collect(tbl(base, 'course'))
teacher = collect(tbl(base, 'teacher'))
score = collect(tbl(base, 'score'))
dbDisconnect(base)
1.查询 “01” 课程比 “02” 课程成绩高的学生的信息及课程分数
必要的数据处理，方便后期调用。
# 学号和课程所有组合下的成绩表
expand.grid(
学号 = pull(student, 学号),
课程编号 = pull(course, 课程编号)
) |>
left_join(
score,
by = c('学号', '课程编号'),
) -> score_longer
score_longer |>
pivot_wider(
names_from = 课程编号,
values_from = 成绩,
names_prefix = '课程'
) -> score_wider
这里直接使用上面的宽表即可。
score_wider |>
filter(课程01 > 课程02) |>
left_join(student, by = '学号')
#> # A tibble: 2 × 7
#> 学号 课程01 课程02 课程03 姓名 生日 性别
#> <chr> <dbl> <dbl> <dbl> <chr> <chr> <chr>
#> 1 02 70 60 80 钱电 1990-12-21 男
#> 2 04 50 30 20 李云 1990-08-06 男
2.查询 “01” 课程比 “02” 课程成绩低的学生的信息及课程分数
score_wider |>
filter(课程01 < 课程02) |>
left_join(student, by = '学号')
#> # A tibble: 2 × 7
#> 学号 课程01 课程02 课程03 姓名 生日 性别
#> <chr> <dbl> <dbl> <dbl> <chr> <chr> <chr>
#> 1 01 80 90 99 赵雷 1990-01-01 男
#> 2 05 76 87 NA 周梅 1991-12-01 女
3.查询平均成绩大于等于 60 分的学生学号、姓名和平均成绩
score |>
summarise(平均成绩 = mean(成绩), .by = 学号) |>
filter(平均成绩 >= 60) |>
left_join(student, by = '学号') |>
select(学号, 姓名, 平均成绩)
#> # A tibble: 5 × 3
#> 学号 姓名 平均成绩
#> <chr> <chr> <dbl>
#> 1 01 赵雷 89.7
#> 2 02 钱电 70
#> 3 03 孙风 80
#> 4 05 周梅 81.5
#> 5 07 郑竹 93.5
4.查询平均成绩小于 60 分的学生的学号和姓名和平均成绩（包括有成绩的和无成绩的）
- 只包括有成绩的
score |>
summarise(平均成绩 = mean(成绩), .by = 学号) |>
filter(平均成绩 < 60) |>
left_join(student, by = '学号') |>
select(学号, 姓名, 平均成绩)
#> # A tibble: 2 × 3
#> 学号 姓名 平均成绩
#> <chr> <chr> <dbl>
#> 1 04 李云 33.3
#> 2 06 吴兰 32.5
- 包括没有成绩的（其中无成绩的而按照 0 分计）
score_longer |>
replace_na(list(成绩 = 0)) |>
summarise(平均成绩 = mean(成绩), .by = 学号) |>
filter(平均成绩 < 60) |>
left_join(student, by = '学号', copy = TRUE) |>
select(学号, 姓名, 平均成绩)
#> 学号 姓名 平均成绩
#> 1 04 李云 33.33333
#> 2 05 周梅 54.33333
#> 3 06 吴兰 21.66667
#> 4 08 王菊 0.00000
5.查询所有学生的学号、姓名、选课总数、所有课程的总成绩
score_longer |>
left_join(student, by = '学号') |>
summarise(
选课总数 = sum(!is.na(成绩)),
总成绩 = sum(成绩, na.rm = TRUE),
.by = c(学号, 姓名)
)
#> 学号 姓名 选课总数 总成绩
#> 1 01 赵雷 3 269
#> 2 02 钱电 3 210
#> 3 03 孙风 3 240
#> 4 04 李云 3 100
#> 5 05 周梅 2 163
#> 6 06 吴兰 2 65
#> 7 07 郑竹 2 187
#> 8 08 王菊 0 0
6.查询 “李” 姓老师的数量
teacher |>
count(是否李姓 = str_detect(教师姓名, '^李'))
#> # A tibble: 2 × 2
#> 是否李姓 n
#> <lgl> <int>
#> 1 FALSE 2
#> 2 TRUE 1
7.查询学过张三老师教授课程的学生信息
这里需要假定上过课的都有分数，所有没有分数的都是因为没上过该门课。
course |>
left_join(teacher, by = '教师编号') |>
filter(教师姓名 == '张三') |>
left_join(score, by = '课程编号') |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 6 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
#> 5 05 周梅 1991-12-01 女
#> 6 07 郑竹 1989-07-01 女
8.找出没有学过张三老师教授课程的学生信息
course |>
left_join(teacher, by = '教师编号') |>
filter(教师姓名 == '张三') |>
left_join(score, by = '课程编号') |>
anti_join(x = student, y = _, by = '学号')
#> # A tibble: 2 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 06 吴兰 1992-03-01 女
#> 2 08 王菊 1990-01-20 女
9.查询学过 “01” 和 “02” 课程的学生信息
score_wider |>
filter(!is.na(课程01), !is.na(课程02)) |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 5 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
#> 5 05 周梅 1991-12-01 女
10.查询学过 “01” 课程，但没有学过 “02” 课程的学生信息
score_wider |>
filter(!is.na(课程01), is.na(课程02)) |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 1 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 06 吴兰 1992-03-01 女
11.查询没有学完全部课程的学生信息
score_longer |>
filter(is.na(成绩)) |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 4 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 05 周梅 1991-12-01 女
#> 2 06 吴兰 1992-03-01 女
#> 3 07 郑竹 1989-07-01 女
#> 4 08 王菊 1990-01-20 女
12.查询至少有一门课与学生 “01” 所学课程相同的学生信息
score |>
filter(学号 == '01') |>
semi_join(x = score, y = _, by = '课程编号') |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 7 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
#> 5 05 周梅 1991-12-01 女
#> 6 06 吴兰 1992-03-01 女
#> 7 07 郑竹 1989-07-01 女
13.查询与学生 “01” 学习的课程完全相同的学生信息
score |>
arrange(学号, 课程编号) |>
summarise(
课程汇编 = paste(课程编号, collapse = ','),
.by = 学号
) |>
filter(课程汇编 == 课程汇编[学号 == '01']) |>
semi_join(x = student, y = _, by = '学号')
#> # A tibble: 4 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
14.同 8 （略）
15.查询两门及以上不及格课程的学生学号，姓名及其平均成绩
score_wider |>
filter(
rowSums(across(-1, \(x) x < 60), na.rm = TRUE) >= 2
# apply(across(-1), 1, \(x) sum(x < 60, na.rm = TRUE) >= 2)
) |>
mutate(
平均成绩 = rowMeans(across(starts_with('课程')), na.rm = TRUE)
# 平均成绩 = apply(across(-1), 1, \(x) mean(x, na.rm = TRUE))
) |>
left_join(student, by = '学号') |>
select(学号, 姓名, 平均成绩)
#> # A tibble: 2 × 3
#> 学号 姓名 平均成绩
#> <chr> <chr> <dbl>
#> 1 04 李云 33.3
#> 2 06 吴兰 32.5
16.检索 “01” 课程分数小于 60，按分数降序排列的学生信息
score |>
filter(课程编号 == '01', 成绩 < 60) |>
arrange(-成绩) |>
left_join(student, by = '学号')
#> # A tibble: 2 × 6
#> 学号 课程编号 成绩 姓名 生日 性别
#> <chr> <chr> <dbl> <chr> <chr> <chr>
#> 1 04 01 50 李云 1990-08-06 男
#> 2 06 01 31 吴兰 1992-03-01 女
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
score_wider |>
mutate(
平均成绩 = rowMeans(across(-1), na.rm = TRUE)
) |>
arrange(-平均成绩)
#> # A tibble: 8 × 5
#> 学号 课程01 课程02 课程03 平均成绩
#> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 07 NA 89 98 93.5
#> 2 01 80 90 99 89.7
#> 3 05 76 87 NA 81.5
#> 4 03 80 80 80 80
#> 5 02 70 60 80 70
#> 6 04 50 30 20 33.3
#> 7 06 31 NA 34 32.5
#> 8 08 NA NA NA NaN
18.查询各科成绩最高分、最低分和平均分，以如下形式显示：
课程编号，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率； 注：及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90
score |>
summarise(
最高分 = max(成绩),
最低分 = min(成绩),
平均分 = mean(成绩),
及格率 = mean(成绩 >= 60),
中等率 = mean(成绩 >= 70 & 成绩 < 80),
优良率 = mean(成绩 >= 80 & 成绩 < 90),
优秀率 = mean(成绩 >= 90),
.by = 课程编号
) |>
left_join(course, by = '课程编号') |>
relocate(课程名称, .after = 课程编号) |>
select(-教师编号)
#> # A tibble: 3 × 9
#> 课程编号 课程名称 最高分 最低分 平均分 及格率 中等率 优良率 优秀率
#> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 01 语文 80 31 64.5 0.667 0.333 0.333 0
#> 2 02 数学 90 30 72.7 0.833 0 0.5 0.167
#> 3 03 英语 99 20 68.5 0.667 0 0.333 0.333
19.按照各科成绩进行排序，并且显示排名
score |>
mutate(排名 = min_rank(-成绩), .by = 课程编号) |>
arrange(课程编号, 排名)
#> # A tibble: 18 × 4
#> 学号 课程编号 成绩 排名
#> <chr> <chr> <dbl> <int>
#> 1 01 01 80 1
#> 2 03 01 80 1
#> 3 05 01 76 3
#> 4 02 01 70 4
#> 5 04 01 50 5
#> 6 06 01 31 6
#> 7 01 02 90 1
#> 8 07 02 89 2
#> 9 05 02 87 3
#> 10 03 02 80 4
#> 11 02 02 60 5
#> 12 04 02 30 6
#> 13 01 03 99 1
#> 14 07 03 98 2
#> 15 02 03 80 3
#> 16 03 03 80 3
#> 17 06 03 34 5
#> 18 04 03 20 6
20.查询学生的总成绩，并进行排名
score_longer |>
summarise(总成绩 = sum(成绩, na.rm = TRUE), .by = 学号) |>
arrange(-总成绩) |>
mutate(排名 = min_rank(-总成绩))
#> 学号 总成绩 排名
#> 1 01 269 1
#> 2 03 240 2
#> 3 02 210 3
#> 4 07 187 4
#> 5 05 163 5
#> 6 04 100 6
#> 7 06 65 7
#> 8 08 0 8
21.查询不同老师所教不同课程平均分从高到低显示
score |>
left_join(course, by = '课程编号') |>
left_join(teacher, by = '教师编号') |>
summarise(
平均成绩 = mean(成绩),
.by = c(教师姓名, 课程名称)
) |>
arrange(-平均成绩)
#> # A tibble: 3 × 3
#> 教师姓名 课程名称 平均成绩
#> <chr> <chr> <dbl>
#> 1 张三 数学 72.7
#> 2 王五 英语 68.5
#> 3 李四 语文 64.5
22.查询所有课程的成绩第 2 至 3 名的学生信息及该课程成绩
score |>
mutate(排名 = min_rank(-成绩), .by = 课程编号) |>
filter(排名 %in% 2:3) |>
arrange(课程编号, -成绩) |>
left_join(student, by = '学号')
#> # A tibble: 6 × 7
#> 学号 课程编号 成绩 排名 姓名 生日 性别
#> <chr> <chr> <dbl> <int> <chr> <chr> <chr>
#> 1 05 01 76 3 周梅 1991-12-01 女
#> 2 07 02 89 2 郑竹 1989-07-01 女
#> 3 05 02 87 3 周梅 1991-12-01 女
#> 4 07 03 98 2 郑竹 1989-07-01 女
#> 5 02 03 80 3 钱电 1990-12-21 男
#> 6 03 03 80 3 孙风 1990-05-20 男
23.统计各科成绩各分数段人数：课程编号，课程名称，[85-100]，[70-85)，[60-70)，[0-60) 及所占百分比
score |>
mutate(
分数段 = cut(
x = 成绩,
breaks = c(0, 60, 70, 85, 101),
right = FALSE
)
) |>
summarise(
人数 = n(),
.by = c(课程编号, 分数段)
) |>
mutate(
百分比 = scales::percent(人数 / sum(人数), accuracy = 0.01),
.by = 课程编号
) |>
left_join(course, by = '课程编号') |>
arrange(课程编号, 分数段) |>
select(-教师编号)
#> # A tibble: 9 × 5
#> 课程编号 分数段 人数 百分比 课程名称
#> <chr> <fct> <int> <chr> <chr>
#> 1 01 [0,60) 2 33.33% 语文
#> 2 01 [70,85) 4 66.67% 语文
#> 3 02 [0,60) 1 16.67% 数学
#> 4 02 [60,70) 1 16.67% 数学
#> 5 02 [70,85) 1 16.67% 数学
#> 6 02 [85,101) 3 50.00% 数学
#> 7 03 [0,60) 2 33.33% 英语
#> 8 03 [70,85) 2 33.33% 英语
#> 9 03 [85,101) 2 33.33% 英语
24.查询学生的平均成绩及名次
score_longer |>
summarise(
平均成绩 = mean(成绩, na.rm = TRUE),
.by = 学号
) |>
arrange(-平均成绩) |>
mutate(排名 = min_rank(-平均成绩))
#> 学号 平均成绩 排名
#> 1 07 93.50000 1
#> 2 01 89.66667 2
#> 3 05 81.50000 3
#> 4 03 80.00000 4
#> 5 02 70.00000 5
#> 6 04 33.33333 6
#> 7 06 32.50000 7
#> 8 08 NaN NA
25.查询各科成绩前三名的记录
score |>
slice_max(成绩, n = 3, by = 课程编号)
#> # A tibble: 10 × 3
#> 学号 课程编号 成绩
#> <chr> <chr> <dbl>
#> 1 01 01 80
#> 2 03 01 80
#> 3 05 01 76
#> 4 01 02 90
#> 5 07 02 89
#> 6 05 02 87
#> 7 01 03 99
#> 8 07 03 98
#> 9 02 03 80
#> 10 03 03 80
26.查询每门课被选修的学生数
count(score, 课程编号)
#> # A tibble: 3 × 2
#> 课程编号 n
#> <chr> <int>
#> 1 01 6
#> 2 02 6
#> 3 03 6
27.查询出只有两门课程的全部学生的学号和姓名
score |>
count(学号) |>
filter(n == 2) |>
semi_join(x = student, y = _, by = '学号') |>
select(学号, 姓名)
#> # A tibble: 3 × 2
#> 学号 姓名
#> <chr> <chr>
#> 1 05 周梅
#> 2 06 吴兰
#> 3 07 郑竹
28.查询男女生人数
count(student, 性别)
#> # A tibble: 2 × 2
#> 性别 n
#> <chr> <int>
#> 1 女 4
#> 2 男 4
29.查询名字中含有风字的学生信息
# student[grep('风', student$姓名), ]
student |>
filter(str_detect(姓名, '风'))
#> # A tibble: 1 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 03 孙风 1990-05-20 男
30.查询同姓名同性别的学生名单，并统计同姓名人数
# 同姓名同性别的学生名单
student |>
count(姓名, 性别) |>
filter(n > 1)
#> # A tibble: 0 × 3
#> # ℹ 3 variables: 姓名 <chr>, 性别 <chr>, n <int>
# 同姓名人数
student |>
count(姓名) |>
filter(n > 1) |>
summarise(人数 = sum(n))
#> # A tibble: 1 × 1
#> 人数
#> <int>
#> 1 0
31.查询 1990 年出生的学生信息
student |>
filter(year(生日) == 1990)
#> # A tibble: 5 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
#> 5 08 王菊 1990-01-20 女
32.计算每门课程的平均成绩，并按降序排列；若平均成绩相同，按课程编号升序排列
score |>
summarise(
平均成绩 = mean(成绩),
.by = 课程编号
) |>
arrange(-平均成绩, 课程编号)
#> # A tibble: 3 × 2
#> 课程编号 平均成绩
#> <chr> <dbl>
#> 1 02 72.7
#> 2 03 68.5
#> 3 01 64.5
33.查询平均成绩大于等于 85 分的学生学号、姓名和平均成绩
score |>
summarise(
平均成绩 = mean(成绩, na.rm = TRUE),
.by = 学号
) |>
filter(平均成绩 >= 85) |>
left_join(student, by = '学号') |>
select(学号, 姓名, 平均成绩)
#> # A tibble: 2 × 3
#> 学号 姓名 平均成绩
#> <chr> <chr> <dbl>
#> 1 01 赵雷 89.7
#> 2 07 郑竹 93.5
34.查询课程名称为数学，且分数低于 60 的学生姓名和分数
score |>
left_join(course, by = '课程编号') |>
filter(课程名称 == '数学', 成绩 < 60) |>
left_join(student, by = '学号') |>
select(姓名, 成绩)
#> # A tibble: 1 × 2
#> 姓名 成绩
#> <chr> <dbl>
#> 1 李云 30
35.查询所有学生的课程及分数情况
score_longer |>
left_join(course, by = '课程编号') |>
left_join(student, by = '学号') |>
select(学号, 姓名, 课程名称, 成绩) |>
pivot_wider(
names_from = 课程名称,
values_from = 成绩
) |>
mutate(
总分 = rowSums(across(-c(学号, 姓名)), na.rm = TRUE)
)
#> # A tibble: 8 × 6
#> 学号 姓名 语文 数学 英语 总分
#> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 01 赵雷 80 90 99 269
#> 2 02 钱电 70 60 80 210
#> 3 03 孙风 80 80 80 240
#> 4 04 李云 50 30 20 100
#> 5 05 周梅 76 87 NA 163
#> 6 06 吴兰 31 NA 34 65
#> 7 07 郑竹 NA 89 98 187
#> 8 08 王菊 NA NA NA 0
36.查询任何一门课程成绩都在 70 分以上的姓名、课程名称和分数
score_longer |>
left_join(course, by = '课程编号') |>
left_join(student, by = '学号') |>
select(学号, 姓名, 课程名称, 成绩) |>
pivot_wider(
names_from = 课程名称,
values_from = 成绩
) |>
filter(
if_all(-c(学号, 姓名), \(x) x > 70)
)
#> # A tibble: 2 × 5
#> 学号 姓名 语文 数学 英语
#> <chr> <chr> <dbl> <dbl> <dbl>
#> 1 01 赵雷 80 90 99
#> 2 03 孙风 80 80 80
37.查询不及格的课程
score |>
left_join(course, by = '课程编号') |>
filter(成绩 < 60) |>
select(学号, 课程编号, 课程名称, 成绩)
#> # A tibble: 5 × 4
#> 学号 课程编号 课程名称 成绩
#> <chr> <chr> <chr> <dbl>
#> 1 04 01 语文 50
#> 2 04 02 数学 30
#> 3 04 03 英语 20
#> 4 06 01 语文 31
#> 5 06 03 英语 34
38.查询课程 01 的成绩大于等于 80 的学生学号和姓名
score |>
filter(课程编号 == '01', 成绩 >= 80) |>
left_join(student, by = '学号') |>
select(学号, 姓名, 成绩)
#> # A tibble: 2 × 3
#> 学号 姓名 成绩
#> <chr> <chr> <dbl>
#> 1 01 赵雷 80
#> 2 03 孙风 80
39.同 26 （略）
40.查询选修 “张三” 老师所授课程的学生中，成绩最高的学生信息及其成绩
teacher |>
filter(教师姓名 == '张三') |>
left_join(course, by = '教师编号') |>
left_join(score, by = '课程编号') |>
slice_max(成绩) |>
left_join(student, by = '学号') |>
select(
教师姓名, 课程名称, names(student), 成绩
)
#> # A tibble: 1 × 7
#> 教师姓名 课程名称 学号 姓名 生日 性别 成绩
#> <chr> <chr> <chr> <chr> <chr> <chr> <dbl>
#> 1 张三 数学 01 赵雷 1990-01-01 男 90
41.查询不同课程成绩相同的学生学号、课程编号、学生成绩
score |>
group_by(学号) |>
count(成绩) |>
filter(n > 1) |>
semi_join(x = score, y = _, by = '学号')
#> # A tibble: 3 × 3
#> 学号 课程编号 成绩
#> <chr> <chr> <dbl>
#> 1 03 01 80
#> 2 03 02 80
#> 3 03 03 80
42.查询每门课程成绩最好的前两名
score |>
slice_max(成绩, n = 2, by = 课程编号)
#> # A tibble: 6 × 3
#> 学号 课程编号 成绩
#> <chr> <chr> <dbl>
#> 1 01 01 80
#> 2 03 01 80
#> 3 01 02 90
#> 4 07 02 89
#> 5 01 03 99
#> 6 07 03 98
43.统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按
人数降序排列，若人数相同，按课程号升序排列
score |>
count(课程编号) |>
filter(n > 5) |>
arrange(-n, 课程编号)
#> # A tibble: 3 × 2
#> 课程编号 n
#> <chr> <int>
#> 1 01 6
#> 2 02 6
#> 3 03 6
44.检索至少选修两门课程的学生学号
score |>
count(学号) |>
filter(n >= 2)
#> # A tibble: 7 × 2
#> 学号 n
#> <chr> <int>
#> 1 01 3
#> 2 02 3
#> 3 03 3
#> 4 04 3
#> 5 05 2
#> 6 06 2
#> 7 07 2
45.查询选修了全部课程的学生信息
score |>
filter(n() == nrow(course), .by = 学号) |>
semi_join(student, y = _, by = '学号')
#> # A tibble: 4 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <chr> <chr>
#> 1 01 赵雷 1990-01-01 男
#> 2 02 钱电 1990-12-21 男
#> 3 03 孙风 1990-05-20 男
#> 4 04 李云 1990-08-06 男
46.查询各学生的年龄: 按照出生日期来算, 当前月日 < 出生年月的月日, 则年龄减 1
student |>
mutate(
年龄 = year(today()) - year(生日) -
(format(Sys.Date(), '%m%d') < format(as.Date(生日), '%m%d'))
)
#> # A tibble: 8 × 5
#> 学号 姓名 生日 性别 年龄
#> <chr> <chr> <chr> <chr> <dbl>
#> 1 01 赵雷 1990-01-01 男 35
#> 2 02 钱电 1990-12-21 男 34
#> 3 03 孙风 1990-05-20 男 35
#> 4 04 李云 1990-08-06 男 34
#> 5 05 周梅 1991-12-01 女 33
#> 6 06 吴兰 1992-03-01 女 33
#> 7 07 郑竹 1989-07-01 女 36
#> 8 08 王菊 1990-01-20 女 35
47.查询本周过生日的学生
student |>
mutate(
当年生日 = as.Date(paste0(
format(Sys.Date(), '%Y'),
format(as.Date(生日), '-%m-%d')
)),
week_start = floor_date(
today(), 'week',
week_start = 1
),
week_end = week_start + 6
) |>
filter(
between(当年生日, week_start, week_end)
)
#> # A tibble: 1 × 7
#> 学号 姓名 生日 性别 当年生日 week_start week_end
#> <chr> <chr> <chr> <chr> <date> <date> <date>
#> 1 07 郑竹 1989-07-01 女 2025-07-01 2025-06-30 2025-07-06
48.查询下周过生日的学生
student |>
mutate(
当年生日 = as.Date(paste0(
format(Sys.Date(), '%Y'),
format(as.Date(生日), '-%m-%d')
)),
week_start = floor_date(
today(), 'week',
week_start = 1
) + 7,
week_end = week_start + 6
) |>
filter(
between(当年生日, week_start, week_end)
)
#> # A tibble: 0 × 7
#> # ℹ 7 variables: 学号 <chr>, 姓名 <chr>, 生日 <chr>, 性别 <chr>,
#> # 当年生日 <date>, week_start <date>, week_end <date>
49.查询本月过生日的学生
student |>
mutate(生日 = ymd(生日)) |>
filter(month(生日) == month(today()))
#> # A tibble: 1 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <date> <chr>
#> 1 07 郑竹 1989-07-01 女
50.查询下月过生日的学生
student |>
mutate(生日 = ymd(生日)) |>
filter(month(生日) == month(today()) + 1)
#> # A tibble: 1 × 4
#> 学号 姓名 生日 性别
#> <chr> <chr> <date> <chr>
#> 1 04 李云 1990-08-06 男