大数据面试题之SQL题
1.有一个录取学生人数表，记录的是每年录取学生人数和入学学生的学制
以下是表结构：
CREATE TABLE admit (
id int(11) NOT NULL AUTO_INCREMENT,
year int(255) DEFAULT NULL COMMENT ‘入学年度’,
num int(255) DEFAULT NULL COMMENT ‘录取学生人数’,
stu_len varchar(255) DEFAULT NULL COMMENT ‘学生学制’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT=‘录取人数’;
year表示学生入学年度
num表示对应年度录取学生人数
stu_len表示录取学生的学制
说明：例如录取年度2018学制3，表示该批学生在校年份为2018~2019、2019~2020、2020~2021
以下是示例数据：
| id | year | num | stu_len |
|---|---|---|---|
| 1 | 2018 | 2000 | 3 |
| 2 | 2019 | 2000 | 3 |
| 3 | 2020 | 1000 | 4 |
| 4 | 2020 | 2000 | 3 |
根据以上示例计算出每年在校人数,写出SQL语句：
SELECT
t2.year,
t2.res
FROM(
SELECT
t1.`year`,
SUM(t1.num) over(ORDER BY t1.`year`) as res,
ROW_NUMBER() over(PARTITION BY t1.`year` ORDER BY t1.`year`) as ranking
FROM(
SELECT year,num FROM admit
UNION ALL
SELECT year+stu_len as year,-num FROM admit
) as t1
ORDER BY t1.`year`
) as t2
WHERE t2.ranking = 1;
计算结果截图：
2.有一个分数表id 是该表的主键。该表的每一行都包含了一场考试的分数。Score 是一个有两位小数点的浮点值。
以下是表结构和数据：
Create table Scores (
id int(11) NOT NULL AUTO_INCREMENT,
score DECIMAL(3,2),
PRIMARY KEY (id)
);
查询并对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是一个连续的整数。
分数表:
| id | score |
|---|---|
| 1 | 3.5 |
| 2 | 3.65 |
| 3 | 4.0 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
根据以上示例数据计算出排名,写出SQL语句不能使用dense_rank和over开窗函数：
select s1.score,
(
-- distinct 去除重复的成绩
select count(distinct s2.score)
from scores s2
where s2.score >= s1.score
) as 'rank'
from scores s1
order by s1.score desc;
计算结果截图：
