Leetcode SQL 刷题 Mysql【2】

最新推荐文章于 2022-07-14 16:20:55 发布

Foneone

最新推荐文章于 2022-07-14 16:20:55 发布

阅读量243

点赞数

分类专栏：刷题 SQL

本文链接：https://blog.csdn.net/foneone/article/details/108102948

版权

刷题同时被 2 个专栏收录

36 篇文章 5 订阅

订阅专栏

SQL

8 篇文章 0 订阅

订阅专栏

【11】595. 大的国家

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

Mysql中的科学计数法

【12】197. 上升的温度

select Id
from Weather as we
where Temperature> (
    select Temperature
    from Weather
    where dateDiff(we.RecordDate,Weather.RecordDate)=1)

select we.Id
from Weather as we left join Weather as w
on w.RecordDate=date_add(we.RecordDate,interval -1 day)
where we.Temperature>w.Temperature

时间相关函数，摘自：https://www.cnblogs.com/xuelu/p/3770478.html

date_add(@dt, interval 1 day)  -- 在dt时间上 add 1 day  
date_add(@dt, interval -1 day); -- 在dt时间上 sub 1 day 减1
adddate() 
addtime() --功能类似
date_sub() --减法
subdate()
subtime()
 
datediff(date1,date2) -- date1-date2  返回天数
timediff(time1,time2) -- time1 - time2 返回 time 差值 类似于 08:08:08
--注意：timediff(time1,time2) 函数的两个参数类型必须相同

思考：为什么不能直接做减法？？为什么给出的例子可以通过，但是13个测试用例不行。类似于下面这样？

select Id
from Weather as we
where Temperature> (
    select Temperature
    from Weather
    where we.RecordDate-Weather.RecordDate=1)

原因：https://www.cnblogs.com/jxterminator/p/4120850.html

（1）如果直接相减，那么就把时间转换成了 2013-04-21 16:59:33 直接转换为20130421165933，进制会出现问题。从而导致问题。（2）还有就是在类似于'2012-01-01'这样的日期的时候会出现问题，减1不能够实现。还是要利用datediff()来做。

【13】184. 部门工资最高的员工

group by 和where位置关系。

MySQL中没有with as子句来创建，但是可以在from 后面直接写临时表结构，来实现类似功能。

在 Mysql中 s inner join t = s join t = s,t ，都是内连接的方式。这道题要注意内连接和left join的区别。因为内连接中where两端都不能为空，因此不会出现 Department's Name is null 的情况。但是 left join 在on时，找不见条件，也会保留左表内容，因此会出现 Department's Name is null 的情况，最后要用where 语句剔除掉这种情况。

select de.Name as Department,t.Name as Employee ,t.Salary --left join
    from (
        select e.Name,e.DepartmentId,e.Salary
        from Employee as e
        where Salary=(
            select max(Salary) 
            from Employee
            where e.DepartmentId=DepartmentId)  ## 临时表t
    ) as t left join Department as de 
    on t.DepartmentId =de.Id
    where de.Name is not null   --临时表查询 写在 where这个where 中 等效

select de.Name as Department,e.Name as Employee, e.Salary  -- inner join 
from Employee as e,Department as de
where e.DepartmentId=de.Id  and e.Salary=( -- 内嵌相关子查询
    select max(Salary)
    from Employee
    where e.DepartmentId = Employee.DepartmentId)

官方解答： in 可以同时判断两个列的情况。

where (a,b) in (
          select a,b
          from table_name)

先找出部门id,和部门最大工资。然后再利用in 内联表中id 和 salary是否对应。从而找到部门最大工资和id。

select de.Name  as Department,ee.Name as Employee,ee. Salary 
from Employee as ee,Department as de 
where ee.DepartmentId=de.id and (ee.DepartmentId,ee.Salary) in (
    select DepartmentId,MAX(Salary)
    from Employee
    GROUP BY DepartmentId )

【14】626. 换座位

(1)利用 union 来做

select a.id,a.student
from (
    select id-1 as id ,student ## 偶数均减1 
    from seat 
    where id mod 2=0
    union 
    select id+1 as id,student ##奇数不一定都加１
    from seat 
    where id mod 2 =1 and id <> (select(max(id)) from seat)
    union
    select id,student
    from seat
    where id mod 2 =1 and id=(select(max(id)) from seat)) as a
order by a.id

（2）利用case when语句来自：@blacksheep

select (case 
    when id mod 2=1 and id =(select max(id) from seat ) then id --顺序不能调换
    when id mod 2=1 then id+1 -- 顺序不能调换
    else id-1
    end ) as id,student
from seat
order by id;

其中包含一个case ..when的知识点在于...他是if elif else 的结构，而不是 if else if else结构。也就是说，当此条件通过的时候，它就执行了，不会再继续执行下面判断。因此 id mod 2=1 and id =(select max(id) from seat ) then id 和 when id mod 2=1 then id+1 不能调换位置。使用过程中应该注意这个点。https://blog.csdn.net/QQ826688096/article/details/89242180

（3）利用 if(exp1,exp2,exp3)来做。可以在任意位置利用 if(exp1,exp2,exp3)。

select (
    if (mod(id,2),if(id=(select max(id) from seat),id,id+1),id-1) 
    )  as id,student
from seat
order by id

【15】177. 第N高的薪水

mysql function的基本结构：https://blog.csdn.net/jssg_tzw/article/details/39694489 高级查询的时候才会用到这种形式

CREATE FUNCTION func_name(input input_type) RETURNS output_type
BEGIN
  set N = n-1; ##set is not in return 
  ## could add if -else
  RETURN ( write SQL ); 
  if expression then 
  return NULL;    
  else return (
  ); ## return 
  end if; ## if 

END ## Begin

（1）用distinct 实现

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET n = N-1　##参数位置
  RETURN (     
  SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT n,1 
  ); ## 用distinct 先筛选后,然后去第N高 
END

但是有个问题， N=-1时，limit能过么？？

（2）先找出前N高的所有数据，然后返回N高中最小的。

知识点：if(exp1,exp2,exp3)表达式不仅可以直接用select语句，还可以直接用from 里面的列变量。

count(*)和count(1)的区别：https://www.cnblogs.com/xiaozengzeng/p/12078845.html

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (     
  SELECT  IF(count<N,NULL,min) 
  FROM
    (SELECT MIN(Salary) AS min, COUNT(1) AS count
    FROM
      (SELECT DISTINCT Salary
      FROM Employee ORDER BY Salary DESC LIMIT N) AS a
    ) as b
  );
END

上面两种方案都来自：https://leetcode-cn.com/problems/nth-highest-salary/solution/liang-ge-hao-li-jie-de-fang-an-by-postor/

【16】262. 行程和用户

知识点：（1）别名处的空格处理（利用的转义字符）：https://www.cnblogs.com/end/archive/2011/04/01/2002516.html

可以直接用' '来就行。里面可以识别空格。

（2）单引号和双引号的区别：https://www.cnblogs.com/xuhaojun/p/9145581.html

https://blog.csdn.net/menghuanzhiming/article/details/77017137

（3）sum() count() 等用法。

count()和 sum() 都是聚合函数，可以与group by一起使用。由于 group by 是按照某一个列进行分组，分组之后每个组内的情况不相同 ,count()等函数都是作用在任一分组上的。他可以计算组内的情况。

sum(if (c.STATUS = 'completed',0,1))

sum()函数分别遍历组内的每个status，直到每个分组都结束。返回的个数是分组之后的个数。

id	request_at/sum()	status
1 2	'2013-10-01'/每个组返回一个sum()	completed
1 2	'2013-10-01'/每个组返回一个sum()	canceled_users
3	'2013-10-2'	canceled_dirver
4	'2013-10-2'	completed

这里也可以解释where为什么必须在group by函数前面。where筛选的是某一行，但是group by之后，不再是每一行单独存在了。所以where要在前面。如果放在后面，有行已经按照分组合并在了一起了，where并不能剔除某一行。having呢？就是作用于分组的，它的作用其实类似于count()和 sum()，只不过他是筛选分组条件。此外where和having两者不冲突。

我的题解（不对的）：

select a.Request_at as Day, round((a.called/b.all_),2) as 'Cancellation Rate'
from
    (select t.Request_at,count(t.Status) as called
    from Trips as t join Users as u on t.Client_Id=u.Users_Id and u.Banned='No'
    where t.Request_at between '2013-10-01' and '2013-10-03'  and t.Status<>'completed '
    group by t.Request_at) as a 
    join ( 
        select t.Request_at,count(t.Status) as all_ 
        from Trips as t join Users as u on t.Client_Id=u.Users_Id and u.Banned='No' 
        where t.Request_at between '2013-10-01' and '2013-10-03' 
        group by t.Request_at ) as b on a.Request_at=b.Request_at      
order by a.Request_at

问题：（1）没有考虑到用户表不是严格的用户，统计的应该是司机和用户都是非禁止的用户才行

（2）利用join 将 a 和b 表连接的时候，会出现丢失日期的问题，因为可能存在一些日期只有非禁止用户总量而没有取消订单的情况。

（3）没有有效的利用除了 statu之外的条件，不会将count()函数灵活运用

（4）连接过程，出现问题。刚开始使用的 left join而不是 join。用left join 会出现左表的内容会全部保留的情况。导致count()计算出现问题。所以要注意到底是用join还是 left join。

题解1：

巧妙的用了avg函数。求解平均数。统计每个分组中 t.Status 中等于'completed' 和不能等于 'completed' 的。

假设不等于'completed' 有10个，等于'completed' 有5个，那么平均值：（10*1+0*5）/(10+5) 将count()转换成了0和1。带了一个条件判断。

select t.Request_at as Day, round(avg(t.Status<>'completed '),2) as 'Cancellation Rate' 
from 
    Trips as t join Users as u on (t.Client_Id=u.Users_Id and u.Banned ='No') 
    join Users as us on t.Driver_Id=us.Users_Id  and us.Banned='No'
where 
    t.Request_at between '2013-10-01' and '2013-10-03' 
group by t.Request_at
order by t.Request_at

题解2：

参考连接中题解2的方法，修改我的。（修改完发现逻辑一样）

select c.Request_at as Day,
        round(sum(if (c.STATUS = 'completed',0,1))/count(c.STATUS),2) as 'Cancellation Rate'
from(
    select t.Request_at,t.Status
    from Trips as t join Users as u on t.Client_Id=u.Users_Id and u.Banned='No'
    join Users as us on t.Driver_Id=us.Users_Id and us.Banned='No' ## 修改第一问题
    where t.Request_at between '2013-10-01' and '2013-10-03'
)  c 
group by c.Request_at
order by c.Request_at

【17】178. 分数排名

rank()是一个窗口函数，详情可以看：https://leetcode-cn.com/problems/rank-scores/solution/tu-jie-sqlmian-shi-ti-jing-dian-pai-ming-wen-ti-by/

解题思路来自：链接1.

select a.Score,(
    select count(distinct b.Score) from Scores as b where b.Score>=a.Score
) as 'Rank'
from Scores as a 
order by Score desc

先按照成绩排序，然后再给每个成绩计算一个名次，如何计算？那么就是有多少个比现在分数高的，那么它就位于哪个名次。有两个比他高的+他自己，那么他就是第三名。

【18】601. 体育馆的人流量

我的题解：思路就是每个id后面，跟四个值。因为连续三个，可以是（id-2,id-1,id）(id-1,id,id+1) (id,id+1,id+2)三种状态。只要有一个满足条件就行。注意的是，子查询的条件（id=a.id+1）。

select st.id,st.visit_date,st.people
from (
    select a.id,a.visit_date,a.people,
    (case when a.id+1<=(select max(id) from stadium) then (select people from stadium where id=a.id+1) else null end ) as after_1,
    (case when a.id+2<=(select max(id) from stadium) then (select people from stadium where id=a.id+2) else null end) as after_2,
    (case when a.id-1>=1 then (select people from stadium where id=a.id-1) else null end) as before_1,
    (case when a.id-2>=1 then (select people from stadium where id=a.id-2) else null end) as before_2
from stadium as a
where a.people>=100) st
where  (st.after_1 >=100 and st.after_2>=100)  or (st.after_1>=100 and st.before_1 >=100)  or (st.before_1>=100 and st.before_2>=100)

官方题解：找id符合的，但是官方题解，先做了一个三表连接。将people>=100的所有id组合列出，然后按照第一天是高峰，第二天是高峰，第三天是高峰，来排除id保留最后结果。假设s1为高峰期,s1可以在第一天出现，第二天出现以及第三天出现。

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

窗口函数的做法来自评论@whisperloli：

SELECT  id,visit_date,people ------/* Write your T-SQL query statement below */
FROM    (
            SELECT  id,visit_date,people,COUNT(*) OVER(PARTITION BY rn) AS cnt
            FROM    (
                        SELECT  *
                                ,(id - row_number() OVER(ORDER BY id)) AS rn
                        FROM    stadium
                        WHERE   people >= 100
                    ) a
        ) d
WHERE   cnt >= 3 ;

（1）内层用一个row_number计数函数，将rn 分成几个部分。因为如果连续三个的话，那么利用 id-row_number应该是相同值。比如 id= 2,3,4，7,8,10 row_number = 1,2,3,4,5,6 id-row_number() = 1,1,1,3,3,4。从而就可以找见连续的区间中的id都实哪些。 -> where 和窗口函数的先后关系是，先利用where筛选后，才会利用窗口函数。窗口函数和select 是同一层级。

（2）外层函数中按照 rn分区，然后找到分区个数内>=3的，那么就是这个rn中至少包含三个连续的。

最后将这些id找出，即可。

【19】185. 部门工资前三高的所有员工

窗口函数的做法：

/* Write your T-SQL query statement below */
select de.NAME Department,a.nAME Employee,a.Salary
from (
    select Name,DepartmentId,Salary,
    dense_rank() over(partition by DepartmentId order by Salary desc ) as row_no
    from Employee
) a join Department de on a.DepartmentId=de.Id
where a.row_no<=3

来自评论：@高斯恍惚对于前N个问题，都可以这样做。

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 e.Id in
(   -- 做自连接,保存比自己工资高的和员工,按照id分组,如果比自己高的不同工资个数小于等于2,
    -- 那么我就在前三中,将id筛选后,再做一层连接
    select e1.Id
    from Employee as e1 left join Employee as e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.Id
    having count(distinct e2.Salary) <= 2
)
order by d.Id asc,e.Salary desc

【20】180. 连续出现的数字

我的题解：

select distinct Num as ConsecutiveNums 
from Logs as lo
where Num = (
    select Num
    from Logs as l1
    where lo.id +1 = l1.id
) and Num=(
    select Num
    from Logs as l2
    where lo.id+2=l2.id
)

问题：如果是连续出现10次那么id怎么写？要连续加么？

评论连接中@山水有相逢2333给出了解决方法。

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

利用了sql的 用户变量。

一共有两种用户变量的赋值方法。 https://blog.csdn.net/weixin_42373127/article/details/88909982

set的使用方法：
set @变量名 {= | := } value [,@变量名 {= | := } value,…] ;

select的使用方法:
select ：=

上面利用用户变量的意思逻辑整理如下：

 prev = null 
 count=null --初始化 
 if prev = num then count +=1 --继续看下一条
 else: 
    prev= num 
    count =1 --初始计算 第一次接触用户变量
相当于 L = [1,1,12,1,2,2]

prev = 0,count=0

for i in range(0,len(L)):
    if prev = L[i]:
        count+=1
    else:
        prev = L[i]
        count = 1
    if count >=3:print(L[i])

Foneone

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Leetcode SQL 刷题 Mysql【2】

【11】595. 大的国家select name,population,area from Worldwhere area > 3000000 or population > 25000000;Mysql中的科学计数法【12】197. 上升的温度select Idfrom Weather as wewhere Temperature> ( select Temperature from Weather where dateDiff(we.
复制链接

扫一扫