sql笔试题

题目一、表t_score，数据如下

name	subject	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

对应ddl及dml语句如下：

CREATE TABLE `t_score` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(8) NOT NULL,
`subject` varchar(4) NOT NULL,
`score` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t_score (name, subject, score) values ('张三', '语文', 81), ('张三', '数学', 75), ('李四', '语文', 76), ('李四', '数学', 90), ('王五', '语文', 81), ('王五', '数学', 100), ('王五', '英语', 90);

1、查出每门课都大于80分的学生姓名。

select name from t_score group by name having min(score) > 80;

2、查出每个学生的最高分及课程名。

with r as (select *, row_number() over(partition by name order by score desc) rn from t_score) select name, score, subject from r where rn = 1;

3、统计每个学生的各科分数及累计分数，显示格式如下：

name	subject	score	accumulate_score
张三	语文	81	81
张三	数学	75	156
李四	语文	76	76
李四	数学	90	166
xxx	xxx	xxx	xxx

select name, subject, score, sum(score) over(partition by name order by id) accumulate_score from t_score; 

4、分数80及以上表示优秀，60及以上表示及格，60以下表示不及格。统计每个学生每一科是否及格、是否优秀，显示格式如下：

name	语文	数学	英语
张三	优秀	及格	不及格
xxx	xxx	xxx	xxx

涉及到行转列，要用case when

with r as (
select name,
sum(case when subject = '语文' then score else 0 end) as `chinese`,
sum(case when subject = '数学' then score else 0 end) as `math`,
sum(case when subject = '英语' then score else 0 end) as `english`
from t_score
group by name
)
select name,
case when chinese < 60 then '不及格' when chinese >= 80 then '优秀' else '及格' end `语文`,
case when math < 60 then '不及格' when math >= 80 then '优秀' else '及格' end `数学`,
case when english < 60 then '不及格' when english >= 80 then '优秀' else '及格' end `英语`
from r;

5、汇总每个人的成绩，显示格式如下：

name	score
张三	语文:81,数学:75
xxx	xxx

select name, group_concat(subject, ':', score) as score from t_score group by name;

group_concat()的用法是：

group_concat([distinct] expr [, expr...] [order by col_name [asc | desc]] [separator str])，返回分组后由其他字段构成的表达式拼接而成的字符串。

如，t_score按name分组后，

name='张三'的这个组的score值有81、75，subject值有语文、数学；

name='李四'的这个组的score值有76、90，subject值有语文、数学；

name='王五'的这个组的score值有81、100、90，subject值有语文、数学、英语。

则select name, group_concat(score) scores from t_score group by name;

会返回：

name	scores
张三	81,75
李四	76,90
王五	81,100,90

select name, group_concat(subject) subjects from t_score group by name;

会返回：

name	subjects
张三	语文,数学
李四	语文,数学
王五	语文,数学,英语

group_concat()可以在括号中用distinct对值去重，用order by对值排序，

如select name, group_concat(score order by score desc) scores from t_score group by name;

会返回：

name	scores
张三	81,75
李四	90,76
王五	100,90,81

默认的拼接符是英文逗号，如何想换成其他的，则需要在括号中用separator关键字显式指定，如指定成中横线，

select name, group_concat(score order by score desc separator '-') scores from t_score group by name;

会返回：

name	scores
张三	81-75
李四	90-76
王五	100-90-81

以上我们在group_concat()的括号中只指定了score，其实可以指定多个字段，中间用逗号分隔即可，而且可以用表达式，如

select name, group_concat(subject, '-', score separator '，') subject_scores from t_score group by name;

会返回：

name	subject_scores
张三	语文-81，数学-75
李四	语文-76，数学-90
王五	语文-81，数学-100，英语-90

题目二、有3个表，建表语句如下：

CREATE TABLE `t_room` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增id',
`room_name` varchar(1) NOT NULL COMMENT '房间号（A、B、C、D）',
`flats_id` int NOT NULL COMMENT '公寓id',
`status` int NOT NULL COMMENT '出租状态。0：未租，1：已租，2：预定',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t_flats` (
`id` int NOT NULL COMMENT '主键',
`flats_num` varchar(8) DEFAULT NULL COMMENT '公寓编号',
`is_private` bit(1) DEFAULT NULL COMMENT '是否是自营房源。1：是，0：不是',
`community_id` int DEFAULT NULL COMMENT '小区id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;