刷leetcode SQL遇到的问题

Atobe的小泪痣

已于 2022-03-22 00:25:40 修改

阅读量669

点赞数

分类专栏：疯狂 SQL 文章标签： sql mysql

于 2022-03-19 14:24:01 首次发布

本文链接：https://blog.csdn.net/weixin_42274933/article/details/123592486

版权

疯狂 SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

刷leetcode SQL遇到的问题

176 Write an SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.
写不出来答案，看solution才知道有这个用法。limit 1 offset 1。
① select * from table limit 2,1;
//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据
② select * from table limit 2 offset 1;
//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条

177 Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null
看似跟176一样，实则不一样，offset后面只能跟数字不能进行计算，所以要新设置一个变量

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT DEFAULT 0;
SET M = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select (select distinct salary from employee
             order by salary desc 
             limit 1 offset M )as getNthHighestSalary
  );
END

178
as 后面的变量名要加引号

197
Write an SQL query to find all dates’ Id with higher temperatures compared to its previous dates (yesterday).
datediff(d1,d2)返回d1和d2日期的差值

620
Write an SQL query to report the movies with an odd-numbered ID and a description that is not “boring”.Return the result table ordered by rating in descending order.
整除:div，5 div 2 = 2;
取余:mod %，5 mod 2 = 1,5 % 2 = 1;

627
Write an SQL query to swap all ‘f’ and ‘m’ values (i.e., change all ‘f’ values to ‘m’ and vice versa) with a single update statement and no intermediate temporary tables.
update的操作不熟悉。

UPDATE salary
SET sex = (CASE sex  WHEN 'm' THEN 'f' ELSE 'm' END);

增 INSERT INTO 表名 VALUES (),(),()…;
删 DELETE FROM 表名 WHERE +条件
改 UPDATE 表名 SET 需要修改的变量名 WHERE +条件
查 SELECT * FROM 表名
其他操作：https://blog.csdn.net/qq_38122518/article/details/80393514