数据库sql题，lc免费，

#方法一：
# Write your MySQL query statement below
# SELECT
#     weather.id AS 'Id'
# FROM
#     weather
#         JOIN
#     weather w ON DATEDIFF(weather.date, w.date) = 1
#         AND weather.Temperature > w.Temperature
 
# SELECT
#     weather.id AS 'Id'
# FROM
#     weather
#         JOIN
#     weather w ON DATEDIFF(weather.date, w.date) = 1
#         AND weather.Temperature > w.Temperature
 
 
# WITH current_recordDate AS(
#  SELECT 
#  month, 
#  FROM 
#  weather
#  GROUP BY 
#  month
# )
# SELECT 
# weather.id AS 'Id',
#  LAG(weather.date,1) OVER (
#  ORDER BY recordDate 
#  ) previous_recordDate
# FROM 
#  weather;
 
 
# SELECT 
# recordDate,
# max(Temperature)
# from
# Weather
# #  GROUP BY 
# #  month
#  ;
#方法二，
# Write your MySQL query statement below
# select 
# id
# from(select
#         id
#         ,Temperature 
#         ,lag(Temperature,1,Temperature) over(order by RecordDate) as l_Temperature
#         ,RecordDate
#         ,lag(RecordDate,1,RecordDate) over(order by RecordDate) as l_RecordDate
#     from Weather
#     order by RecordDate 
# )l
# where Temperature > l_Temperature and  l_RecordDate = date_sub(RecordDate,INTERVAL 1 DAY)
 
# with tmp as(
#     select
#         id
#         ,Temperature 
#         ,lag(Temperature,1,Temperature) over(order by RecordDate) as l_Temperature
#         ,RecordDate
#         ,lag(RecordDate,1,RecordDate) over(order by RecordDate) as l_RecordDate
#     from Weather
#     order by RecordDate 
# )
# select id 
# from tmp where Temperature > tmp.l_Temperature and tmp.l_RecordDate = date_sub(tmp.RecordDate,INTERVAL 1 DAY)
 
#方法三
with old_Weather as(
    select
        id
        ,lag(Temperature,1,Temperature) over(order by RecordDate) as l_Temperature
        ,lag(RecordDate,1,RecordDate) over(order by RecordDate) as l_RecordDate
    from Weather
    order by l_RecordDate 
), 
current_Weather as(
    select
        id,
        Temperature,
        RecordDate
    from Weather
    order by RecordDate 
)
select current_Weather.id 
from current_Weather 
left join old_Weather
on current_Weather.id=old_Weather.id
where current_Weather.Temperature > old_Weather.l_Temperature 
;
 
 
 

 

 

# # Write your MySQL query statement below
# select
# salary as SecondHighestSalary,
# rank()over(order by salary desc) as rk
# from Employee
# where rk=2;
 
# 方法一：
# SELECT
#     (SELECT DISTINCT
#             Salary
#         FROM
#             Employee
#         ORDER BY Salary DESC
#         LIMIT 1 OFFSET 1) AS SecondHighestSalary
 
 
# 方法二：
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary
 
 
 
 

题目】
现在有“课程表”，记录了学生选修课程的名称以及成绩。

现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生，那么查询应返回 null。

【解题思路】
1.找出所有选修了“语文”课的学生成绩

select *
from 成绩表
where 课程='语文';
2.查找语文课程成绩的第二名

考虑到成绩可能有一样的值，所以使用distinct 成绩进行成绩去重。

思路1：
使用子查询找出语文成绩查询最大的成绩记为a，然后再找出小于a的最大值就是课程成绩的第二高值。
max(列名) 可以返回该列的最大值
可以用下面的sql语句得到语文课的最大值

select max(distinct 成绩)
from 成绩表
where 课程='语文';
然后再找出小于a的最大值就是课程成绩的第二高值。

select max(distinct 成绩)
from 成绩表
where 课程='语文' and
      成绩 < (select max(distinct 成绩)
              from 成绩表
              where 课程='语文');
思路2：使用 limit 和 offset

在《猴子 从零学会sql》中讲过：

limit n子句表示查询结果返回前n条数据

offset n表示跳过x条语句

limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。

select distinct 成绩  
from 成绩表
where 课程='语文'
order by 课程,成绩 desc
limit 1,1;
3.考虑特殊情况

题目要求，如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。

ifnull(a,b)函数解释：

如果value1不是空，结果返回a

如果value1是空，结果返回b

对于本题的sql就是：

select ifnull(第2步的sql,null) as '语文课第二名成绩';
我们把第2步的sql语句套入上面的sql语句，本题最终sql如下：

select ifnull(
(select max(distinct 成绩) from 成绩表
where 成绩<(select max(成绩) from 成绩表 where 课程='语文')
and 课程='语文')
,null) as '语文课第二名成绩';
【本题考点】
1）第二高的查询思路，利用本题的解决办法可以解决这类问题：查询第N高的数据

2） limit字句的用法

3） ifnull的用法

【举一反三】
查找 Employee 表中第二高的薪水（Salary）。查询结果返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null

暂时看到：：：：：：：：：：：：