leetcode_数据库

175.组合两个表

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

176.第二高的薪水

1
2
#MySQL中的IFNULL函数类似于Oracle中的NVL函数,其表达式为:IFNULL(expr1,expr2),含义是:如果第一个参数不为空,则返回第一个参数,否则返回第二个参数。
select IFNULL((select DISTINCT Salary from Employee order by Salary DESC limit 1,1),NULL) as SecondHighestSalary

177.第N高的薪水

1
2
3
4
5
6
7
8
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)
);
END

178.分数排名

  • 查询去重后分数的,条件:当前行分数大于等于同表的分数的count数量,去重显示,然后降序输出
1
select Score,(select COUNT(DISTINCT Score) from Scores where Score >= s.Score)  as Rank from Scores as s order by Score DESC

180.连续出现的数字

  • Id 从1开始递增的,只需要查找出 Id 连续且 Num 相等的数字即可
1
2
3
4
select distinct l1.Num as ConsecutiveNums 
from Logs as l1, Logs as l2, Logs as l3
where l1.Id = l2.Id-1 and l2.Id = l3.Id-1
and l1.Num = l2.Num and l2.Num = l3.Num

181.超过经理收入的员工

  • 只需要员工的Salary大于ManagerId的Salary即可
1
2
select em.Name as Employee from Employee em, Employee em1
where em.Salary > em1.Salary and em.ManagerId = em1.Id

182.查找重复的电子邮箱

1
2
select distinct P1.Email from Person P1,Person P2
where P1.Email = P2.Email and P1.Id != P2.Id

183.从不订购的客户

  • 识别null是用’is’搭配的而不是’=’
1
2
3
select C.Name as Customers from Customers C 
left join Orders O on C.Id = O.CustomerId
where O.CustomerId is null

184.部门工资最高的员工

  • 两个表内联一下,找出员工Salary,DepartmentID在group by DepartmentId最大薪水的列表中即可

  • 1
    2
    3
    select D.Name Department, E.Name Employee, E.Salary
    from Employee E inner join Department D on E.DepartmentId = D.Id
    where (E.Salary,E.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId)

185.部门工资前三高的员工

  • 取前三高: 再加入一张Employee表,与第一张Employee表做对比,让E2表中Salary大于E1表中Salary的个数少于3就能得到前三高
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
条件语句中必须为 3 > (...),子查询中必须是E2.Salary > E1.Salary

不妨假设E1=E2=[6,5,4,3],则子查询的过程如下:

1、E1.Salary=3;则E2.Salary可以取4、5、6;COUNT(DISTINCT E2.Salary)=3

2、E1.Salary=4;则E2.Salary可以取5、6;COUNT(DISTINCT E2.Salary)=2

3、E1.Salary=5;则E2.Salary可以取6;COUNT(DISTINCT E2.Salary)=1

4、E1.Salary=6;则E2.Salary无法取值;COUNT(DISTINCT E2.Salary)=0

则要令COUNT(DISTINCT E2.Salary)  < 3 的情况有上述的4、3、2.

也即是说,这等价于取e1.Salary最大的三个值。
1
2
3
4
5
6
select D.Name Department, E1.Name Employee, E1.Salary Salary
from Employee E1 inner join Department D on E1.DepartmentId = D.ID
where 3 > (
select count( distinct E2.Salary ) from Employee E2
where E1.Salary < E2.Salary and E1.DepartmentId = E2.DepartmentId)
order by Department,Salary desc

196.删除重复的电子邮箱

  • 删除邮箱相同且Id大的项
1
delete P1 from Person P1, Person P2 where P1.Email = P2.Email and P1.Id > P2.Id

197.上升的温度

  • DATE数据类型要用to_days函数返回从年份0开始的天数来判断昨天
1
select W1.Id from Weather W1, Weather W2 where to_days(W1.RecordDate) = to_days(W2.RecordDate)+1 and W1.Temperature > W2.Temperature

262.行程和用户

  • 两位小数用round函数实现,日期在2013年10月1日 至 2013年10月3日可以用between实现,但是要注意日期格式
1
2
3
4
5
select Request_at as Day,
round(count(if (Status != 'completed',True,null) ) / count(*),2) as 'Cancellation Rate'
from Trips T inner join Users U on T.Client_Id = Users_Id
where U.Banned != 'YES' and T.Request_at between '2013-10-01' and '2013-10-03'
group by T.Request_at

595.大的国家

1
select name,population,area from World where population > 25000000 or area > 3000000

596.超过5名学生的课

1
select class from (select distinct * from courses) c group by class having count(student) >= 5

601.体育馆的人流量

  • 拆分成三个表,s1,s2,s3,当显示s1的时候分别有三种情况:
  • s1 s2 s3
  • s2 s1 s3
  • s2 s3 s1

  • 可能会有重复出现的情况,用distinct即可解决

1
2
3
4
5
6
7
8
select distinct s1.* from stadium s1, stadium s2, stadium s3 
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
and (
(s1.id = s2.id-1 and s2.id = s3.id-1)
or (s2.id = s1.id-1 and s1.id = s3.id-1)
or (s2.id = s3.id-1 and s3.id = s1.id-1)
)
order by s1.id

620.有趣的电影

  • 奇数用求除2的余数不为0来表示
1
2
3
select id, movie, description, rating from cinema 
where description != 'boring' and id % 2 != 0
order by rating desc

626.换座位

  • 用联合查询来实现id的互换而不是名字的互换,所有数据分成三类,一类是偶数id的id-1,一类是奇数id但是不是最后一个的id+1,最后一类就是id是奇数但是是最后一个所以id不变
1
2
3
4
5
6
7
8
select id,student from 
(
select id-1 as id,student from seat where id % 2 = 0
union
select id+1 as id,student from seat where id % 2 = 1 and id != (select count(*) from seat)
union
select id as id,student from seat where id % 2 = 1 and id = (select count(*) from seat)
) s order by id

627.交换工资

1
update salary set sex = if(sex = 'm','f','m')