Mysql06-SQL习题
select FirstName, LastName, City, State
from Person left join Address on Person.PersonId = Address.PersonId;
-- 方式一 使用临时表解决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;
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
-- 计算分数的名次,计算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
-- 方式一
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;
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;
select Email from Person group by Email having count(1) > 1;
-- 当group by分组后有重复的数据,可以使用any_value()随意取值。
any_value(id)
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;