Mysql06-SQL习题

Mysql06-SQL习题

1.175. 组合两个表

select FirstName, LastName, City, State 
from Person left join Address on Person.PersonId = Address.PersonId;

2.176. 第二高的薪水

-- 方式一 使用临时表解决null的问题。
select (select Salary from Employee group by Salary order by Salary desc limit 1,1) as SecondHighestSalary;

-- 方式二 使用ifnull，解决null的问题。
select (ifnull((select Salary  from Employee group by Salary order by Salary desc limit 1,1), null) ) as SecondHighestSalary;

-- 方式三 使用distinct去重
select (ifnull((select distinct Salary  from Employee order by Salary desc limit 1,1), null) ) as SecondHighestSalary;

3.177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select (ifnull((select distinct Salary  from Employee order by Salary desc limit N,1), null) ) as SecondHighestSalary
  );
END

4.178. 分数排名

-- 计算分数的名次，计算70分的名次
select count(distinct b.Score) from Scores b where b.Score >= 70;

select a.Score Score, 
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) `Rank`
from Scores a order by a.Score desc;

select Score, dense_rank() over(order by Score desc) 'Rank' from Scores

-- over对应的四个函数。
-- rank()，对over排序进行处理，会计算相同的值。
80	1
80	1
80	1
70	4
70	4
60	6
50	7

-- dense_rank()，对over排序处理，不会计算相同的值。
80	1
80	1
80	1
70	2
70	2
60	3
50	4

-- row_number()，按照行统计。
80	1
80	2
80	3
70	4
70	5
60	6
50	7

-- ntile(2) over(order by Score desc)，将数据评价分到两个桶中，并显示桶号。
80	1
80	1
80	1
70	1
70	2
60	2
50	2

5.180. 连续出现的数字

-- 方式一
select distinct a.Num ConsecutiveNums from Logs a 
where exists (select id from Logs b where b.id = a.id - 1 and b.num = a.num) 
and exists (select id from Logs b where b.id = a.id + 1 and b.num = a.num);

-- 方式二
select distinct a.Num as ConsecutiveNums from Logs a,Logs b, Logs c 
where b.Id = a.Id - 1 and c.Id = a.Id + 1 
and b.Num = a.Num and c.Num = a.Num; 

6.181. 超过经理收入的员工

select Name Employee from Employee a where ManagerId is not null 
and exists (select b.id from Employee b where a.ManagerId = b.Id and a.Salary > b.Salary);

-- 方式二，join相当与inner join，显示共有的数据
select a.Name Employee from Employee a join Employee b on a.ManagerId = b.Id and a.Salary > b.Salary;

7.182. 查找重复的电子邮箱

select Email from Person group by Email having count(1) > 1;

-- 当group by分组后有重复的数据，可以使用any_value()随意取值。
any_value(id)

8.183. 从不订购的客户

select a.Name Customers  from Customers a left join Orders b 
on a.Id = b.CustomerId where b.CustomerId is null;

-- 方式二
select Name Customers  from Customers where Id not in (select CustomerId from Orders);

9.查询平均分最高的学生姓名

-- 数据
-- 姓名	科目	分数
-- 张三	语文	60
-- 张三	数学	70
-- 张三	英语	80
-- 李四	语文	70
-- ...
select name,avg(number) from tb_student group by name order by avg(number) desc limit 1;

10.联表更新

-- 两张表
-- 学生平均成绩表
CREATE TABLE `tb_number` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `avg_number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- 学生表
CREATE TABLE `tb_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- 从tb_student表中计算出学生的平均成绩，然后将平均成绩按照对应的姓名更新到tb_number表中。
update tb_number n, (select name,avg(number) n from tb_student group by name) s set n.avg_number = s.n where s.name = n.name;