Leetcode数据库

175. 组合两个表

select FirstName,LastName,City,State
from Person
left join Address using(PersonId)

176. 第二高的薪水

select (
    select distinct salary 
    from Employee 
    order by salary desc 
    limit 1,1) as SecondHighestSalary

177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select(
            select distinct salary
            from (
                select 
                    salary 
                    ,dense_rank() over(order by salary desc) as rk
                from Employee 
            ) t
            where rk=n
        ) 
  );
END

178. 分数排名

# Write your MySQL query statement below

select 
    score       
    ,dense_rank() over(order by score desc) as `rank`
from Scores
order by `rank` asc

180 连续出现的数字

select distinct num as ConsecutiveNums 
from (select 
        num
        ,id-cast(dense_rank() over (partition by num order by id asc) as signed)  as rn 
      from logs) t1
group by rn,num
having count(*)>=3

181 超过经理收入的员工

select e1.name as Employee 
from Employee e1
left join Employee e2 on e1.managerId =e2.id
where e1.salary>e2.salary

182 查找重复的电子邮箱

select Email
from Person 
group by Email   
having count(*)>=2

183 从不订购的客户

select Name as  Customers 
from Customers c
left join Orders o on c.id=o.CustomerId 
where CustomerId  is null

184 部门工资最高的员工

select   Department,Employee,Salary
from(
    select
        d.name as Department 
        ,e.name as Employee 
        ,salary as Salary 
        ,dense_rank() over(partition by departmentId order by salary desc) as rk
    from Employee e
    left join Department d on e.departmentId =d.id
) t
where rk=1

185 部门工资前三高的所有员工

select   Department,Employee,Salary
from(
    select
        d.name as Department 
        ,e.name as Employee 
        ,salary as Salary 
        ,dense_rank() over(partition by departmentId order by salary desc) as rk
    from Employee e
    left join Department d on e.departmentId =d.id
) t
where rk in(1,2,3)

196 删除重复的电子邮箱

delete u
from Person u , Person v
where v.id < u.id and u.email = v.email

197 上升的温度

select id
from(
    select
        id
        ,recordDate 
        ,Temperature 
        ,date_add(lag(recordDate,1) over(order by recordDate ),interval 1 day) as last
        ,lag(Temperature,1) over(order by recordDate ) as tomorrow
    from Weather
) t
where Temperature>tomorrow and datediff(last,recordDate)=0

262 行程和用户

select 
    request_at as Day
    ,round(avg(case when status='completed' then 0 else 1 end),2) as "Cancellation Rate"
from Trips t
left join Users u1 on t.client_id = u1.users_id 
left join Users u2 on t.driver_id = u2.users_id
where  (request_at between "2013-10-01" and  "2013-10-03")
and u1.banned ='No' and u2.banned ='No'
group by request_at 
order by request_at

595 大的国家

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

596 超过5名学生的课

select class   
from Courses
group by class    
having count(1)>=5

601 体育馆的人流量

select id,visit_date,people
from(
    select id,visit_date,people,
            count(*) over(partition by rk) as rn
    from (
        select id,visit_date,people,cast(row_number() over(order by visit_date) as signed)-id as rk 
        from Stadium 
        where people>=100   
    ) a
) b
where rn>=3
order by visit_date

620 有趣的电影

select *
from cinema
where id%2=1 and description != 'boring'
order by rating desc

626 换座位

select
    case 
    when id%2=1 and id =(select count(*) from Seat) then id
    when id%2=1 then id+1
    else id-1
    end as id,student 
from Seat
order by id asc;

627 变更性别

update Salary 
set sex=if(sex='m','f','m')

1179 重新格式化部门表

select 
     id 
    ,sum(case when month='Jan' then revenue else null end) as Jan_Revenue 
    ,sum(case when month='Feb' then revenue else null end) as Feb_Revenue
    ,sum(case when month='Mar' then revenue else null end) as Mar_Revenue
    ,sum(case when month='Apr' then revenue else null end) as Apr_Revenue
    ,sum(case when month='May' then revenue else null end) as May_Revenue
    ,sum(case when month='Jun' then revenue else null end) as Jun_Revenue
    ,sum(case when month='Jul' then revenue else null end) as Jul_Revenue
    ,sum(case when month='Aug' then revenue else null end) as Aug_Revenue
    ,sum(case when month='Sep' then revenue else null end) as Sep_Revenue
    ,sum(case when month='Oct' then revenue else null end) as Oct_Revenue
    ,sum(case when month='Nov' then revenue else null end) as Nov_Revenue
    ,sum(case when month='Dec' then revenue else null end) as Dec_Revenue
from Department
group by id