力扣日记——数据库篇（3）

最新推荐文章于 2024-04-29 19:03:05 发布

m0_51334054

最新推荐文章于 2024-04-29 19:03:05 发布

阅读量108

点赞数

分类专栏：力扣数据库

本文链接：https://blog.csdn.net/m0_51334054/article/details/119531704

版权

力扣数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

力扣日记——数据库篇（3）

178

在这里插入图片描述
统计大于等于该项的项数作为rank，然后将score降序排列

select s1.Score as "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;

184

在这里插入图片描述
注意in的用法，先把每个部门最高工资找出来，再用in去找

select d.Name as Department,e.Name AS Employee,e.Salary from Department d,Employee e where d.Id=e.DepartmentId and
(d.Id,e.salary) in(select DepartmentId,max(salary) from Employee group by DepartmentId)

626

在这里插入图片描述
题目意思是奇偶互换，我一开始想用update，后来发现不能实例化
You can’t specify target table ‘s’ for update in FROM clause，
看了一圈好像只能用select的QAQ

修改id，是偶数就减一，是奇数如果是最后一个即id总数则不变，否则加一，最后排下序

select 
    if(id%2=0,id-1,if(id=cnt,id,id+1)) as id,
    student 
from (select count(*) as cnt from seat)as a,seat 
order by id;

177

在这里插入图片描述
注意limit用法，排第2高就limit 1,1，然后limit后面不能跟减法，要在外面先减一下，另外注意要加desc降序排列

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

180

在这里插入图片描述

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

思路上比较简单，但这个方法可拓展性太低了，万一数字变了就不好使，以后再想更通用的吧

185

终于刷到困难了
在这里插入图片描述
思路不难，和前面两题加起来差不多
前三高用count两张表来找，再把department和employee连接起来

select d.name as Department,e1.name as Employee ,e1.Salary from Employee e1 join Department d ON e1.DepartmentId=d.Id where (select count(distinct e2.Salary) from Employee e2 where e1.Salary<e2.Salary and e1.DepartmentId=e2.DepartmentId)<3

262

在这里插入图片描述
慢慢琢磨。。主要是两部分

select 
    request_at as 'Day',
     ROUND(
			SUM(
                case t.status 
                when 'cancelled_by_driver' then 1 
                when 'cancelled_by_client' then 1
                else 0 end
			)
			/ 
			COUNT(T.id),
			2
	) AS `Cancellation Rate`
from 
    trips t 
join users u1 on t.Client_id = u1.Users_Id and u1.Banned = 'No'
join users u2 on t.Driver_Id  = u2.Users_Id and u2.Banned = 'No'
where request_at between '2013-10-01' and '2013-10-03'
group by request_at
order by request_at

601

在这里插入图片描述
本来想和上面180差不多

select distinct s1.Id,s1.visit_date,s1.people from Stadium s1,Stadium s2,Stadium s3 where s1.Id=s2.Id-1 and s2.Id=s3.Id-1 and s1.people>=100 and s2.people>=100 AND s3.people>=100

发现漏了
在这里插入图片描述
发现是没有考虑最后三行，还发现要order一下

select distinct s1.Id,s1.visit_date,s1.people from Stadium s1,Stadium s2,Stadium s3 where ((s1.Id=s2.Id-1 and s2.Id=s3.Id-1) or(s1.Id=s2.Id+1 and s2.Id=s3.Id-2) or(s1.Id=s2.Id+1 and s2.Id=s3.Id+1)) and s1.people>=100 and s2.people>=100 AND s3.people>=100 order by visit_date