sql语句练习

1.有两个表：student,score

student表
 	id	name	age	height
1	1	zhangsan 21	80
2	2	lisi	32	90
3	3	wangwu	12	70
4	4	zhaoxin	68	50
5	5	huahua	23	45
6	6	hehe	21	45
7	7	chengcheng	24	70

score表

 	id	class	score
1	1	chinese	98
2	1	math	89
3	1	english	98
4	2	chinese	78
5	2	math	100
6	2	english	88
7	3	chinese	58
8	3	math	34
9	3	english	99

// 逻辑：关联找出 各科分数最高的学生名字
select name,`class`,score from student s inner join score c on s.id=c.id(内连接)

`class` 
//partition by 新增一列，然后筛选出maxsore=score 的那一行，如果有多行，那就并列
select * from (select *,max(score) over(partition by `class`) as maxscore from score)sm where score=maxscore ;
// join student
select s.name,sc.`class`,sc.score from student s,(select * from (select *,max(score) over(partition by `class`) as maxscore from score)sm where score=maxscore)sc where s.id=sc.id

select s.name,sc.`class`,sc.score from student s inner join (select * from (select *,max(score) over(partition by `class`) as maxscore from score)sm where score=maxscore)sc on s.id=sc.id

 	name	class	score
1	zhangsanchinese	98
2	lisi	math	100
3	wangwu	english	99

2.

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

//不考虑不存在第二高的薪水，那么查询应返回 null：

select min(ss.salary)as SecondHighestSalary  from (select  Salary from employee order by salary desc limit 2)ss

// 考虑

 select min(xx.sec)SecondHighestSalary from (

select (case cc.c when cc.c<2 then null else bb.Salary end)sec

from (select count(distinct Salary)c from employee)cc,

(select  Salary from employee order by salary desc limit 2)bb)xx
解析：本题考查 case when then  .. else ..end 的应用

3.编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

select score,dense_rank()over( order by score desc) as Rank from scores;（通过9/10个测试用例）

解析：本题考查：runk(),row_number(),dense_runk()的应用与区别

4.编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

select distinct l1.num ConsecutiveNums from Logs l1 join Logs l2 on l1.id=l2.id-1 join Logs l3 on l1.id=l3.id-2 where l1.num=l2.num and l2.num=l3.num

解析：本题考查自连接 ，自连接3次 

5.Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

分3步：

1. join

select d.name as department,e.name as employee,e.salary from employee e inner join department d on e.departmentid=d.id

2.为每个分区生成 dense_rank（注意 dense_rank,rank,row_number的区别）

select *,dense_rank()over(partition by t.department order by t.salary desc)r from (select d.name as department,e.name as employee,e.salary from employee e inner join department d on e.departmentid=d.id)t

3. 筛选出row_number<=3 的所有记录

select x.department,x.employee,x.salary from(select *,dense_rank()over(partition by t.department order by t.salary desc)r from (select d.name as department,e.name as employee,e.salary from employee e inner join department d on e.departmentid=d.id)t)x where x.r<4

解析：本题考查 分区排序 下的filter

6.