sql力扣_力扣sql

简单

595.大的国家

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

596.超过5名学生的课

select
  class
from 
  course
group by 
  class 
having 
  count(distinct student) >= 5

196.删除重复的邮箱

delete p1
from 
  Person p1,
  Person p2
where
  p1.Email = p2.Email
and
  p1.Id > p2.Id

181.超过经理收入的员工

select
  e1.Name as Employee
from 
  Employee e1
inner join
  Employee e2
on 
  e1.MangerId = e2.Id
and
  e1.Salary > e2.Salary

620.有趣的电影

select
  id,movie,description,rating
from 
  cinema
where 
  mod(id,2)=1
and 
  description <> 'boring'
order by rating desc

select
  id,movie,description,rating
from 
  cinema
where 
  mod(id,2)=1
and 
  description <> 'boring'
order by rating desc

197.上升的温度

select w2.Id
from 
  Weather w1
join 
  Weather w2
on date_add(w1.RecordDate,INTERVAL 1 day) = w2.RecordDate
and w1.Temperature < w2.Temperature

176.第二高的薪水

select 
  MAX(e.Salary) as SecondHighestSalary
from 
  Employee e
where
  e.Salary < 
  (select max(e1.Salary) 
  from 
    Employee e1
  )

627.交换工资

update salary set sex=case when sex='f' then 'm' else 'f' end;

中等

180.连续出现的数字

select distinct l.Num as ConsecutiveNums from Logs l,Logs l1,Logs l2
where l.Id = l1.Id -1
and l.Id = l2.Id - 2
and l.Num = l1.Num
and l.Num = l2.Num

184.部门工资最高的员工

select 
  d.Name as Department,
  e.Name as Employee,
  e.Salary
from 
  Employee e
inner join 
  Department d
on
  e.DepartmentId = d.Id
where 
  e.Salary >= (select max(Salary) from Employee e1 where e1.DepartmentId = e.DepartmentId  group by DepartmentId)

626.换座位

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

177.第N高薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee e where N = (select count(distinct Salary) from Employee where Salary >= e.Salary )
 
  );
END

178.分数排名

SELECT 
  Score, 
  (SELECT count(DISTINCT score)  FROM Scores WHERE score >= s.score) AS Rank 
FROM
  Scores s 
ORDER BY 
  Score DESC ;

困难

185.部门工资前三高

select 
  d.name as Department ,
  e.name as Employee,
  e.Salary as Salary
from 
  Employee as e
inner join
  Department as d
on 
  e.DepartmentId = d.Id
where 
  (
     select 
        count(distinct salary)
      from
        Employee e1
      where
        e1.Salary> e.Salary
      and
        e1.DepartmentId = e.DepartmentId 
  )<3
  order by e.DepartmentId,e.Salary desc

601.体育馆的人流量

select  distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
) order by a.id