一天刷完LeetCode上所有SQL题

title多少有点震惊党的意思，但说实话，这还不是因为LeetCode太抠了，才给20道MySQL的题目。而且，这里的题目有些很简单，简单地离谱，有些则复杂的难以言喻（现在来看觉得当时有点点傻，哈哈哈哈）；学习还是不能走太多的捷径，加油吧少年。

LeetCode175 - 组合两个表

# 是个简单题，思路也很简单，就是通过左连接
select
    FirstName, LastName, City, State
from
    Person left join Address on Person.PersonId=Address.PersonId;

LeetCode176 - 第二高的薪水

# 思路也很简单，基本上就是对排序+去重操作的考察，order by + distinct，关键点在于没有的情况下返回null，这里只要再套一层查询即可
select
    (select
        distinct Salary
    from
        Employee
    order by
        Salary desc limit 1,1
    ) as SecondHighestSalary;

LeetCode177 - 第N高的薪水

# 题目跟前面那一题差不多，唯一需要注意的是，N需要提前设定为N-1，因为索引是从0开始的
# 直接设置为N-1，貌似会报错
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary desc limit N, 1
  );
END

LeetCode178 - 分数排名

# 可以使用两张表联合查询实现分数排名
select
	s1.Score, count(distinct(s2.Score)) as 'Rank'
from
	Scores s1, Scores s2
where
	s1.Score <= s2.Score
group by
	s1.Id
order by
	s1.Score desc;

LeetCode180 - Z字形变换

select distinct Num as ConsecutiveNums from
    (select Num,
        case
            when @prev = Num then @count := @count + 1
            when (@prev := Num) is not null then @count := 1
        end as CNT
    from Logs, (select @prev := null, @count := null) as t
)as temp
where temp.CNT >=3;

LeetCode181 - 超过经理收入的员工

select
	e1.Name as Employee
from
	Employee e1, Employee e2
where
	e1.ManagerId = e2.Id
and
	e1.Salary > e2.Salary;

LeetCode182 - 查找重复的电子邮箱

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

LeetCode183 - 从不订购的客户

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

LeetCode184 - 部门工资最高的员工

select    d.Name as Department,    e.Name as Employee,    e.Salaryfrom    Employee e,    Department dwhere    e.DepartmentId=d.Id    and    (e.Salary, e.DepartmentId) in (select max(Salary), DepartmentId from Employee group by DepartmentId);

LeetCode185 - 部门工资前3高的所有员工

select    d.Name as Department,    e2.Name as Employee,    e2.Salaryfrom    Employee e1,    Employee e2,    Department dwhere    e1.DepartmentId=e2.DepartmentIdand    e2.DepartmentId=d.Idand    e1.Salary >= e2.Salarygroup by    e2.Salary,    e2.Name,    Departmenthaving    count(distinct e1.Salary) <= 3order by    Department,    e2.Salary desc;

LeetCode196 - 删除重复的电子邮箱

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

LeetCode197 - 上升的温度

select    a.Idfrom    Weather a join     Weather bon    a.Temperature > b.Temperatureand    dateDiff(a.recordDate, b.recordDate) = 1;

LeetCode262 - 行程和用户

select    t.request_at Day,    (        round(count(if (t.status != 'completed', t.status, null)) / count(t.status), 2)    ) as 'Cancellation Rate'from    Users u inner join Trips t on u.Users_id = t.Client_Id,    Users f inner join Trips n on f.Users_id = n.Driver_Idwhere    t.request_atbetween    '2013-10-01'and    '2013-10-03'and    u.banned != 'Yes'and    f.banned != 'Yes'and    t.Id = n.Idgroup by    t.request_at;

LeetCode595 - 大的国家

select     name,     population,     areafrom    Worldwhere    area > 3000000or    population > 25000000;

LeetCode596 - 超过5名学生的课

select    classfrom    (select        class,        count(*) as count_num    from         (            select                distinct student, class            from                courses        ) as c    group by        class    ) as numwhere    count_num >= 5;

LeetCode601 - 体育馆的人流量

select    distinct a.*from    stadium a,    stadium b,    stadium cwhere    (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+2 and a.id=c.id+1))order by    a.visit_date;

LeetCode620 - 有趣的电影

select    *from    cinemawhere    description!='boring'and    id%2=1order by    ratingdesc;

LeetCode626 - 换座位

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, studentfrom    seatorder by    id;

LeetCode627 - 变更性别

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

LeetCode1179 - 重新格式化部门表

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