大数据面试题之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
以下是示例数据：

根据以上示例计算出每年在校人数,写出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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

计算结果截图：

2.有一个分数表id 是该表的主键。该表的每一行都包含了一场考试的分数。Score 是一个有两位小数点的浮点值。
以下是表结构和数据：
Create table Scores (
id int(11) NOT NULL AUTO_INCREMENT,
score DECIMAL(3,2),
PRIMARY KEY (id)
);

查询并对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是一个连续的整数。

分数表:

根据以上示例数据计算出排名,写出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;
1
2
3
4
5
6
7
8
9

计算结果截图：