sql 差值_sql面试题重点（持续更新中。。。）

本文将收集较难或者解法奇妙的sql面试题作为复习题集，积累在于平时的点点滴滴，加油！

1.查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名

sc-成绩表 ：sid，cid，score

teacher-教师表：tid，tname

student-学生表：sid，sname，sage，ssex

course-课程表：cid，cname，tid

SELECT DISTINCT sc.sid FROM sc 
WHERE sid not in (
SELECT sid FROM sc
WHERE cid not in 
(SELECT cid FROM sc WHERE sid = 01))
GROUP BY sid 
HAVING COUNT(DISTINCT cid)=(SELECT count(cid) FROM sc WHERE sid =01)
and sid !=01

本题在于用not in找出和01同学学的课程不同的同学学号，再用not in除去该学生，然后利用groupby having 去重计数找到学课程完全一样的的学生，最后排除01学生。

2.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT * FROM 
(SELECT *,
row_number() over(PARTITION by cid ORDER BY score desc ) num 
FROM sc ) a
WHERE num in (2,3)

子查询中用row_number() over()分组排序，再利用where in 定位位置找到分组中第二与第三行。

3.查询每门功课成绩最好的前两名

方法一：窗口函数

SELECT *FROM 
(SELECT *,row_number() over(PARTITION by cid ORDER BY score desc) num_score 
FROM sc ) a 
WHERE a.num_score<=2

row_number 和 子查询

方法二：子查询

SELECT * FROM sc c
WHERE 2>(SELECT COUNT(sid) FROM sc WHERE c.cid=cid AND score>c.score)
ORDER BY c.cid,c.score desc

4.查询下周过生日的学生

SELECT sid,sname,sage
FROM student
where WEEKOFYEAR(sage)=WEEKOFYEAR(date_add(CURDATE(),INTERVAL 1 week))

日期函数weekofyear 周索引，curdate当前年月日，date_add日期偏移

----------------------------------------------------------------------------------------

5.table1（id：自增id，money：费用）问题：按id顺序累加money，取出累计值与1000相差最小差值的id。

select id from (
select id, abs(sum(money) over(order by id)-1000) m from table1) a
order by m
limit 1

6.

select id from weather a,weather b 
where a.temperature<b.temperature
and datediff(a.recorddate,b.recorddate)=1

考察datediff函数、自连接