leetcode从sql起步

175. 组合两个表

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

176. 第二高的薪水

select e.Salary as SecondHighestSalary
from employee e right join (select e1.salary from employee e1 ORDER BY e1.Salary desc)t
on t.salary>e.Salary
ORDER BY e.Salary DESC LIMIT 1

(这套题比较无聊，主要是考连接的，因为里面有null，所以需要用连接来搞)

177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare n1 int;//变量的声明
set n1=n-1;//变量的设置
  RETURN (
      # Write your MySQL query statement below.
      select distinct
      salary from employee order by salary desc limit n1,1
  );
END

//这里主要考察编写函数

178. 分数排名

select r2.score as Score,t3.rank as Rank
from Scores r2 left join
(select count(*) as rank,t1.score from
(select distinct r.score
from Scores r)t1 left
join
(SELECT distinct r1.score
from Scores r1)t2
on t1.score<=t2.score
GROUP BY t1.score
ORDER BY rank) t3
on r2.score=t3.score
ORDER BY t3.rank

//这里由于使用了mysql所以复杂了点，但是只要注意思想，该题还是很简单

180. 连续出现的数字

select l3.num as ConsecutiveNums
from logs l1 left join logs l2 on l1.num=l2.num and l1.id=l2.id-1
left join logs l3 on l2.num=l3.num and l2.id=l3.id-1
where l3.num is not null

//简单，只要审清题目

181. 超过经理收入的员工

select e1.`name` as Employee
from employee e1
where e1.salary>(
    select e2.salary
    from employee e2
    where e1.ManagerId=e2.id
)
//这道题比较经典，需要注意相关子查询方面的内容

182. 查找重复的电子邮箱

select distinct p2.email
from person p1 left join person p2
on p1.email=p2.email and p1.id<>p2.id
where p2.email is not null

//比较简单，应该有更好的方法

183. 从不订购的客户

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

184. 部门工资最高的员工

select d.name as Department,e.Name as Employee,e.salary
from Department d,Employee e
where e.DepartmentId=d.Id and e.salary=(select max(salary) from Employee where DepartmentId=d.id);

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

# select t.department as Department,t.`name` as Employee,t.salary
# from
# (
# select count(*) as rank,e1.`name`,e1.salary,d.`name` as department,e1.departmentId
# from Employee e1 left join Employee e2 on e1.salary<=e2.salary and e1.departmentId=e2.departmentId left join department d on e2.departmentId=d.id
# GROUP BY e1.`name`,e1.departmentId
# ) t
# where t.rank<=3
# ORDER BY t.departmentId asc,t.salary desc

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 where salary>e.salary
and departmentid=e.DepartmentId )<3 order by e.departmentid,Salary desc

//这里注意第二种写法，第一种是自己想的，但是确实是很罗嗦

196. 删除重复的电子邮箱

delete p1 from person p1,person p2 where p1.email=p2.email and p1.id>p2.id

197. 上升的温度

select w1.id
from weather w,weather w1
where w.temperature<w1.temperature and
DATEDIFF(w.RecordDate,w1.RecordDate)=-1

262. 行程和用户

select Request_at as Day,
    round(count(if (status != 'completed', true, null))/ count(*), 2) as 'Cancellation Rate'
from Trips
where (Request_at between '2013-10-01' and '2013-10-03' )
    and Client_id not in (
        select Users_Id from Users where Banned='Yes'
    ) and  Driver_id not in (
        select Users_Id from Users where Banned='Yes'
    )
group by Request_at

//这里主要是学了几个函数的用法，比如round(),还有if()函数的用法。

595. 大的国家

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

 

596. 超过5名学生的课

select class
from courses
group by class
having count(distinct student)>=5
//注意数据质量，如果一个学生选了两次课，可能就会造成重复统计

601. 体育馆的人流量

select *
from stadium s
where people>=100
and s.date+1 in (select s2.date from stadium s2 where s2.people>=100 and s2.date+1 in (
    select s3.date
    from stadium s3
    where people>=100
))
UNION
select *
from stadium s
where people>=100 and
s.date-1 in (
    select s2.date
    from stadium s2
    where s2.people>=100 and s2.date-1 in (
        select s3.date
        from stadium s3
        where people>=100
    )
)

//自己写了一个比较烂的，多表联合查询可以轻易解决

//这里有一个问题，对日期相加减是比较危险的，应该正确选择字段，id如果是主键的话可能是一个比较好的选择。

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

//注意这里三天的写法，其中其一天是后面两天为大于100，中间一天是前一天和后一天人数大于100，后面一天是前两天人数大于100，注意这种思路。

 

620. 有趣的电影

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

 

626. 换座位

select * 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+1<=(select count(*) from seat)
    union
    select id,student from seat where id%2=1 and id+1>(select count(*) from seat)
) as t
ORDER BY id asc

//这个是比较笨的办法，但是是报表整理的一个思路

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

//这个是报表整理的常见思路，还有注意区分case xxx when简单匹配和搜索case when的区别

627. 交换工资

update salary set sex=(case sex when 'm' then 'f' when 'f' then 'm' end)

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

//这个纯属联系case when和if