力扣数据库记录（免费题库的）

简单中等：
184:
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

±—±------±-------±-------------+
| Id | Name | Salary | DepartmentId |
±—±------±-------±-------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
±—±------±-------±-------------+
Department 表包含公司所有部门的信息。

±—±---------+
| Id | Name |
±—±---------+
| 1 | IT |
| 2 | Sales |
±—±---------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
±-----------±---------±-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

select d.name as department,m.name as employee,m.salary from  
(select max(salary) as salary ,departmentid   from employee e  group by e.departmentid ) t join employee m on t.departmentid=m.departmentid join department d on d.id=t.departmentid where t.salary=m.salary 

如果直接用下面的，会缺失一个结果，max（salary）只会选取一个最大的

select d.name as department,e.name as employers,salary from employee e join Department d on e.DepartmentId=d.id group by e.departmentid  having max(salary)

196.删除重复的电子邮箱

delete  p1 from person p1 join person p2 on p1.email=p2.email where p1.id>p2.id

选择id较大的行删除

197.上升的温度
datediff(2008-12-29,2008-12-30)=1

select w2.id from weather w1 join weather w2 where w1.temperature<w2.temperature and datediff(w1.RecordDate,w2.RecordDate)=-1

596 超过5名学生的课
注意DISTINCT的使用是为了防止一个学生对应的多节相同课程；

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

1179:重新格式化部门表
case when then else :对字段值进行判定，并返回你设定的值

626.换座位

select if(id%2=0,id-1,(if id=(select count(distinct id from seat),id,id+1)) from seat order by id

困难：
185 部门工资前三高的所有员工
用sql server和oracle 写的，用了分区函数 partition by,排名函数dense_rank() over() 函数，因为mysql中rank()函数要手写，暂时就没用mysql

select d.name as department, t.name as employee,t.salary from (select dense_RANK() OVER(partition by departmentid order by salary desc) as rank,salary,departmentid,name from employee) t join department d on t.departmentid=d.id where t.rank<=3 order by d.name, t.salary desc 

在oracle中奇慢无比。。。

262行程和用户
参考讨论中的第一个，先计算出非禁止用户生成的订单，在计算里面 被取消的订单

SELECT Request_at as 'day',round(sum(if (status='completed',0,1))/count(id),2) as 'Cancellation Rate' from trips t join users u on t.client_id=u.users_id where u.banned='no' and t.Request_at between '2013-10-01' and '2013-10-03' group by Request_at

601 体育馆的人流量
找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。
先找出三天连续的id,
这个可用于很多场景，不过不知道大数据还能不能跑得动

SELECT distinct a.*
FROM stadium as a,stadium as b,stadium as c
where ((a.id = b.id-1 and b.id+1 = c.id) or
       (a.id-1 = b.id and a.id+1 = c.id) or
       (a.id-1 = c.id and c.id-1 = b.id))
  and (a.people>=100 and b.people>=100 and c.people>=100)
order by a.id;

作者：little_bird
链接：https://leetcode-cn.com/problems/human-traffic-of-stadium/solution/ti-yu-guan-de-ren-liu-liang-by-little_bird/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。